mysql - Laravel 查询生成器 - 获取表结果 `as`

Question

我要在 Laravel 中实现 SQL 查询。我的查询是:

SELECT *  FROM officer_ranks ra
LEFT JOIN (SELECT * FROM officer_rank_details WHERE void = 0) AS r ON  ra.rank = r.present_rank
LEFT JOIN officer_languages l ON r.officer_id = l.officer_id
GROUP BY(ra.rank)
ORDER BY ra.id ASC;

为了实现这个查询，我写了:

DB::table('officer_ranks as ra')
->leftJoin(DB::raw('SELECT * FROM officer_rank_details WHERE void = 0'), 'ra.rank', '=', 'present_rank')
->leftJoin('officer_languages as l', 'officer_id', '=', 'l.officer_id')
->groupBy('ra.rank')
->get();

我的问题是如何获取 (DB::raw(...)) 结果表作为命名 r 因为我需要 JOIN表。

我试过用

leftJoin(DB::raw('SELECT * FROM officer_rank_details WHERE void = 0') as r, 'ra.rank', '=', 'present_rank')

但是没有成功。

Answer 1

我在评论中写了如何修复您的代码。但是你原来的 SQL 应该是:

SELECT *  FROM officer_ranks ra
LEFT JOIN officer_rank_details AS r 
    ON  ra.rank = r.present_rank
    AND r.void  = 0
LEFT JOIN officer_languages l ON r.officer_id = l.officer_id
ORDER BY ra.id ASC;

在 Laravel 中:

DB::table('officer_ranks as ra')
    ->leftJoin('officer_rank_details as r', function($join){
       $join->on('ra.rank', '=', 'r.present_rank');
       $join->on('r.void',  '=', 0);
    })
    ->leftJoin('officer_languages as l', 'r.officer_id', '=', 'l.officer_id')
    ->get();

我还删除了 GROUP BY 子句，因为它的使用方式没有意义。