Level 1
use laravel ORM
Mar 14, 2019
4
Level 1
From SQl to Laravel Query builder
I have this SQL query but had no idea how to write it in Laravel Query Builder
select state_id, state_name, sum(inactifs) as inactifs, sum(actifs) as actifs,
sum(inactifs) + sum(actifs) as total
from
(
select distinct s.id as state_id, s.name as state_name, u.id as user_id,
case when uga.id is null then 1 else 0 end as inactifs,
case when uga.id is null then 0 else 1 end as actifs
from users u
inner join states s on u.state_id = s.id
left join user_group_affiliations uga on uga.user_id = u.id
and (uga.active = 1 and (uga.start_date is null or uga.start_date <= now()) and (uga.end_date is null or uga.end_date >= now()))
) quer group by state_id;
Level 75
Practice writing these things and understand it takes some trial and error sometimes.
So many existing conversion examples given already I recently gave a couple myself.
https://laravel.com/docs/5.8/queries
https://laracasts.com/discuss/channels/eloquent/convert-raw-sql-to-eloquent
You could also use your working query as is. Laravel converts right back to normal SQL at runtime.
no idea how to write it in Laravel Query Builder
That's where you practice the examples given in the documentation and view some of the free video series already referenced in the link I gave in the other post.
1 like
Level 1
@jlrdw thx mate
$main_query->from('users')
->select(
DB::raw('distinct s.id as state_id, s.name as state_name, u.id as user_id,
case when uga.id is null then 1 else 0 end as inactifs,
case when uga.id is null then 0 else 1 end as actifs
')
)
->join('states as s', 'u.state_id', '=', 's.id')
->leftJoin('user_group_affiliations as uga', 'uga.user_id', '=', 'u.id')
->where('uga.active', 1)
->where(function($query){
return $query->whereNull('uga.start_date')
->orWhere('uga.start_date' '<=' date_create())
})
->where(function($query){
return $query->whereNull('uga.end_date')
->orWhere('uga.end_date' '>=' date_create())
})->groupBy('u.state_id') }, "quer")->get();
Level 75
@LADHARI - And if by chance you ever need a complex query written regular in the db facade here is an excellent example from cronix
https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
ever need a custom paginator:
https://laracasts.com/discuss/channels/guides/length-aware-paginator
Practice some simple eloquent relations now:
https://laravel.com/docs/5.8/eloquent
https://laravel.com/docs/5.8/eloquent-relationships
Start with easy examples, work up slowly.
1 like
Please or to participate in this conversation.