Convert SQL to Laraval Eloquent 5

Question

I’m trying to turn this SQL from the postgres below to the eloquent in the laraval someone has an idea of how it would look?

select 
  g.id as group_id,
  max(g.name) as name,
  max(g.created_at) as created_at,
  count(ag.asset_id) as veichele
from "groups" g left join assets_groups ag on g.id = ag.group_id 
group by g.id;

Answer 1

If your model eloquent is with the name Groups is basically:

$select = 'groups.id as group_id, max(groups.name) as name,';
$select .= 'max(groups.created_at) as created_at,';
$select .= 'count(assets_groups.asset_id) as veichele';

$result = Groups::groupBy('groups.id')
      ->leftJoin('assets_groups', 'assets_groups.group_id', '=', 'groups.id')
      ->selectRaw($select);

this would be the construction of Query Builder.