Eloquent select total query

Question

Eloquent select total query

Navigation

#1 by (2 votes)

2

I'm trying to group to bring totals into my query that is in mysql

 SELECT COUNT(*) as total
       ,e.descricao
  FROM pedido_cliente p
  INNER JOIN estabelecimentos e on e.id = p.estabelecimento_id
  GROUP BY p.estabelecimento_id,descricao

What brings the following result

total  |  descricao
   1   |   spermercado
   20  |   mercadinho
   10  |   loja

In eloquent format

$totais = Pedido_cliente::with('estabelecimento')
                        ->select(DB::raw('count(*) as total' ,'estabelecimento'),
                        ->groupBy('estabelecimento_id')
                        ->get()

Where estabelecimento_id is the foreign key of column id of table estabelecimentos

But the description column comes empty

table establishment

id   | descricao 
 1   | Supermercado
 2   | Mercadinho
 3   | Loja

customer request

id   | estabelecimento_id | pedido
 1   |     1              | 5
 2   |     3              | 4
 3   |     2              | 3

Model Customer Order

class Pedido_cliente extends Model
{
    protected $table    = 'pedido_cliente';        

    public function estabelecimento() {
        return $this->belongsTo('App\Estabelecimento', 'estabelecimento_id');
    }
}

Template establishment

class Estabelecimento extends Model
{
}

Why is the description null?

php laravel laravel-eloquent

asked by anonymous 07.12.2018 / 18:22

1 answer

ngx-bootstrap + angular 6 + datepicker How to concatenate one vector in another elegantly in ADVPL?

score 2 · Accepted Answer

Just put estabelecimento_id in your SQL and by the advanced load ratio get descricao from the estabelecimento table, eg:

$totais = Pedido_cliente::with('estabelecimento')
                        ->select(DB::raw('count(*) as total','estabelecimento_id'),
                        ->groupBy('estabelecimento_id')
                        ->get()


foreach($totais as $t)
{ 
    $total = $t->total;
    $descricao = $t->estabelecimento->descricao;
}

In this particular case, that is enough.