with complicated left joins

I'm 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 builder? I'm confused on how to used the laravel join query builder with this type of query.

thanks!

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

---

我相信这应该工作：

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

---

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

链接地址: http://www.djcxy.com/p/18094.html

上一篇: 取消长时间操作的最佳方法是什么？

下一篇: 左连接复杂