Converting a complex sql query to query builder

Hay does anybody have any idea how to turn this SQL query into a laravel builder query?

select * from (
  select `entry_date` as "entry_since",
  @streak := @streak+1 streak, 
  datediff(curdate(),`entry_date`) diff
  from (
    select distinct date(`entry_date`) `entry_date`
    from tracker_entries where user_id = {$this->id} and tracker_id = {$trackerId}
  ) t1
  cross join (select @streak := -1) t2
  order by `entry_date` desc
  ) 
t1 where streak = diff
order by streak desc 
limit 1;

you can shove it all in a db::select

\DB::select("....");

but it still seems a little brutal for laravel, any ideas?

jlrdw

7 years ago

Level 75

This has been asked over and over.

And eloquent query or QB is something like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('max(dc_pets.petid) as maxPetId')
                ->where('dc_powners.ownerid', '<', 3)
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

OR use relations:

https://laravel.com/docs/5.8/eloquent-relationships

OR use as is:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Use some trial and error to work out the exact query.

Numerous examples here:

https://laravel.com/docs/5.8/queries

Please or to participate in this conversation.