Count lines

Question

Good am using Laravel 5.2 and I have much doubt to count the lines related to my research. I need to return the number of products within Categorias and SubCategorias.

My relationship is as follows, in the table Product I have a field Cdsubcategory that references the Cdsubcategory of the table of Subcategory, besides this I have within the table of Subcategory the code of the category, this due to the relationship (one for many for category and subcategory) as in the image below.

In my model Product I made like this:

class Produto extends Model
{
 use SoftDeletes;

 protected $fillable = [
    'CdSubCategoria',
    'NmProduto',
    'DscProduto',
    'VlUnit',
    'UnitEmEstoque',
    'FlgDescontinuado',
    'FlgProdutoVisivel',
    'Visivel_Ini',
    'Visivel_Fim',
    'FlgPontua',
    'QtdPontos',
    'MaxPontosPorSubCategoria'
];
protected $primaryKey = 'CdProduto';
protected $dates = ['deleted_at'];

public function subCategoria()
{
    return $this->belongsTo('App\SubCategoria','CdSubCategoria','CdSubCategoria');
}

public function imagens(){
    return $this->hasMany('App\Imagem', 'CdProduto', 'CdProduto');
}

public function lotes(){
    return $this->hasMany('App\LoteProduto', 'CdProduto', 'CdProduto');
}

public function tipo_produto_embalagem(){
    return $this->belongsTo('App\TipoProdutoEmbalagem', 'CdProduto', 'CdProduto');
 }
}

Already no model Categoria:

class Categoria extends Model
{
 use SoftDeletes;

protected $fillable = ['NmCategoria', 'DscCategoria'];
protected $primaryKey = 'CdCategoria';
protected $dates = ['deleted_at'];

public function subCategorias()
{
    return $this->hasMany('App\SubCategoria','CdCategoria','CdCategoria');
 }
}

And finally in the Subcategory model:

class SubCategoria extends Model
{
 use SoftDeletes;

 protected $fillable = ['CdCategoria','NmSubCategoria', 'DscSubCategoria', 'FlgPontua', 'QtdPontos', 'MaxPontosCategoria'];
 protected $primaryKey = 'CdSubCategoria';
 protected $dates = ['deleted_at'];

 public function categoria()
 {
    return $this->belongsTo('App\Categoria','CdCategoria','CdCategoria');
 }

 public function produtos(){
    return  $this->hasMany('App\Produto','CdSubCategoria','CdSubCategoria');
 }
}

Counting the number of products within a subcategory I managed in a static way, and I did as follows:

 $count = Produto::where('CdSubCategoria','3')->count();
 dd($count);

But I have some problems like:

1. I need to count all the subCategorias, ie this count needs to be dynamic. It is worth remembering that I need this information in my view, because I am using it so:

   CATEGORY NAME (QTY PRODUCT)

2. I need in addition to the quantity of products per subCategoria It is necessary product by categories (Remembering that this relationship is one for many)

OBS.

The ideal is that this information comes together with my model. Ex: the same way I access $produto->imagens could access $subCategoria->qtd and $categoria->qtd

Answer 1

I think what you’re looking for can be done with withCount. This method you use to bring the counting of relationships.

Behold:

$produtos = Produto::with('imagens')->withCount('imagens')->where(['created_at' => new DateTime])->get();

To access the count value, simply add _count in front of the relationship name:

 $produto->imagens_count; // 3

Answer 2

1) Accurate counts all subcategories, that is, this count needs to be dynamic. Remember that I need this information in my view, because I’m using it like this: CATEGORY NAME (QTY PRODUCT)

The withCount for it to work properly in your case it has to be SubCategoria for Produto:

$s = new SubCategoria();
$r = $s->withCount("produtos")->get();
var_dump($r);

the return will show something:

[
    {
        "CdSubCategoria": 1,
        "CdCategoria": 1,
        "NmSubCategoria": "Sub Categoria 1",
        "DscSubCategoria": "Sub Categoria 1",
        "deleted_at": null,
        "produtos_count": 2
    },
    {
        "CdSubCategoria": 2,
        "CdCategoria": 1,
        "NmSubCategoria": "Sub Categoria 2",
        "DscSubCategoria": "Sub Categoria 2",
        "deleted_at": null,
        "produtos_count": 1
    },
    {
        "CdSubCategoria": 3,
        "CdCategoria": 2,
        "NmSubCategoria": "Sub Categoria 3",
        "DscSubCategoria": "Sub Categoria 3",
        "deleted_at": null,
        "produtos_count": 1
    }
]

---

2) Precise in addition to the quantity of products per subcategory is required product by categories (Remembering that this relationship is one for many)

$c = new Categoria();
$r = $c
     ->join('sub_categorias','sub_categorias.CdCategoria','=','categoria.CdCategoria')
     ->join('produtos','produtos.CdSubCategoria','=','sub_categorias.CdSubCategoria')
     ->selectRaw('categoria.CdCategoria,categoria.NmCategoria, count(*) count_produto_cat')
     ->groupBy(['categoria.CdCategoria','categoria.NmCategoria'])
     ->get();

the return will show something:

[
    {
        "CdCategoria": 1,
        "NmCategoria": "Categoria 1 ",
        "count_produto_cat": 3
    },
    {
        "CdCategoria": 2,
        "NmCategoria": "Categoria 2",
        "count_produto_cat": 1
    }
]

Observing: It is worth remembering that Produtos has no direct relationship with Categoria