Laravel 4 query builder - with complex left joins

Question

I am new to laraval 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 constructor? I am confused about how to use the laravel join query builder with this type of query.

thanks!

Answer!!

Will all petkostas help on the laravel forum. We have received an 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(); 

Answer 1

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(); 

Answer 2

This type of query is very difficult to build using the query builder. However, you can use DB::select

if you have nothing to bind, you can use the following:

 DB::select("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"); 

if you need to associate the parameter with the request:

 $deleted = 0; DB::select("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 = ?", [$deleted]); 

Answer 3

I believe using ORM relationships is the best way to join tables.

please follow the link: https://laravel.com/docs/5.7/eloquent-relationships