Consultation Eloquent select total

Question

I am trying to group to bring totals in 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

Which brings the following result

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

In eloquent form

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

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

But the description column comes empty

table establishing

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 request

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

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

Model establishing

class Estabelecimento extends Model
{
}

Why the description is void?

Answer 1

Just put estabelecimento_id in his SQL and by the forward charge ratio take the descricao table estabelecimento, example:

$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 that case, that’s enough.