php - Laravel 子连接选择查询

Question

我正在尝试找出如何在 Laravel 中复制以下查询。我想要做的是从 table2/t2 获取最新状态并将其与我们的主要记录 table1/ 合并t1

select id, created_at, t2.status from table1 t1

left join table2 t2 ON 52.id = (
    SELECT MAX(id) from table2
    where table2.submission_id = submission_id
) AND t1.id = t2.submission_id

上面的查询非常有效，但我不知道如何在“原生”Laravel 中复制它 -

$query = Table1::query();
$query->join('table2 as t2', function (JoinClause $join) {
    $join->selectRaw('MAX(id)')
        ->where('t2.submission_id', '=', 'submission_id');
}, '=', 't2.id');

我也试过 joinSub -

$query->joinSub(function (Builder $query) {
                $query
                    ->selectRaw('MAX(id)')
                    ->from('table2 as t2')
                    ->where('t2.submission_id', '=', 'bespoke_submission_id');
            }, 't2', 't1.id', '=', 't2.id', 'left');

但是我似乎无法得到我想要的结果!有什么想法吗？

Answer 1

此代码可能有效，

DB::table('table1 t1')
->select(['id', 'created_at', 't2.status'])
->leftJoin('table2 t2', function ($join) {
    $subQuery = DB::table('table2')
                    ->selectRaw('MAX(id)')
                    ->whereRaw('table2.submission_id = submission_id');
    $join->on('t2.id', DB::raw('(' . $subQuery->toSql() . ')'))->whereRaw('t1.id = t2.submission_id');
});

或者这个

DB::table('table1 t1')
->select(['id', 'created_at', 't2.status'])
->leftJoin('table2 t2', function ($join) {
    $subQuery = DB::raw('(select MAX(id) from table2 where table2.submission_id = submission_id)');
    $join->on('t2.id', $subQuery)->whereRaw('t1.id = t2.submission_id');
});

注意:您可以使用->toSql() 方法在执行之前打印出查询字符串，以确保您确实得到了您想要的查询。