I'm new to Laravel 4.
I have this query:
SELECT a.id, active, name, email, img_location, IFNULL(b.Total, 0) AS LeadTotal, IFNULL(c.Total, 0) AS InventoryTotal
FROM users AS a
LEFT JOIN (
SELECT user_id, count(*) as Total
FROM lead_user
GROUP BY user_id
) AS b ON a.id = b.user_id
LEFT JOIN (
SELECT user_id, count(*) as Total
FROM user_inventory
GROUP BY user_id
) AS c ON a.id = c.user_id
WHERE a.is_deleted = 0
How can I convert it to Laravel query builder? I'm confused on how to use the Laravel join query builder with this type of query.
Answer!!
Will all the help of petkostas on laravel forum. We got the answer.
$users = DB::table('users AS a')
->select(array('a.*', DB::raw('IFNULL(b.Total, 0) AS LeadTotal'), DB::raw('IFNULL(c.Total, 0) AS InventoryTotal') ) )
->leftJoin(DB::raw('(SELECT user_id, COUNT(*) as Total FROM lead_user GROUP BY user_id) AS b'), function( $query ){
$query->on( 'a.id', '=', 'b.user_id' );
})
->leftJoin(DB::raw('(SELECT user_id, COUNT(*) as Total FROM user_inventory WHERE is_deleted = 0 GROUP BY user_id) AS c'), function( $query ){
$query->on( 'a.id', '=', 'c.user_id' );
})
->where('a.is_deleted', '=', 0)
->get();
I believe this should work:
$users = DB::table('users')
->select( array('users.*', DB::raw('COUNT(lead_user.user_id) as LeadTotal'), DB::raw('COUNT(user_inventory.user_id) as InventoryTotal') ) )
->leftJoin('lead_user', 'users.id', '=', 'lead_user.user_id')
->leftJoin('user_inventory', 'users.id', '=', 'user_inventory.user_id')
->where('users.is_deleted', '=', 0)
->get();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With