Laravel 4 query builder - with complicated left joins

Question

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

Mend

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

Lekishalela

The output is not correct. It doubles the counting when it founds a record between lead_user and user_inventory tables. – Mend

Answer 2

This type of query is very hard to build with query builder. However, you can use DB::select

if you have nothing to bind, you can use 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 need to bind parameter with the query:

$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]);

Endermic

I can used this, but I think there is a way to manage it using query builder join. Thanks anyway. This will be my last resort. – Mend

Answer 3

I believe, use of ORM relations is a better way to join the tables.

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

Tuneful