Laravel 4查询构建器-具有复杂的左联接 [英] Laravel 4 query builder - with complicated left joins

问题描述

我是Laravel 4的新手.

I'm new to Laravel 4.

我有这个查询:

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

如何将其转换为Laravel查询生成器?我对如何在这种类型的查询中使用Laravel联接查询生成器感到困惑.

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.

答案！

请在laravel论坛上对petkostas的所有帮助.我们得到了答案.

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

这篇关于Laravel 4查询构建器-具有复杂的左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！