Laravel中SQL子查询关联转Eloquent/Query Builder求助

原SQL查询语句：

SELECT opp_id, risk_study.rst_id, risk_study.rst_status
FROM opportunity
         LEFT JOIN risk_study ON risk_study.rst_id =
             (SELECT risk_study_quote_vehicle.rst_id
              FROM risk_study_quote_vehicle
                       INNER JOIN quote_vehicle ON quote_vehicle.quv_id = risk_study_quote_vehicle.quv_id
                       INNER JOIN quote ON quote.quo_id = quote_vehicle.quo_id
              WHERE quote.opp_id = opportunity.opp_id
              ORDER BY risk_study_quote_vehicle.rst_id DESC
              LIMIT 0,1)
WHERE 1 = 1
  AND opportunity.per_id_process = '5'
  AND opportunity.opp_locked = '1'

尝试的Laravel代码（无法获取risk_study表数据）：

$opportunities = Opportunity::select('opp_id', 'risk_study.rst_id', 'risk_study.rst_status')
    ->leftJoin('risk_study', function ($join) {
        $join->on('risk_study.rst_id', '=', DB::raw('"SELECT risk_study_quote_vehicle.rst_id FROM risk_study_quote_vehicle INNER JOIN quote_vehicle ON quote_vehicle.quv_id = risk_study_quote_vehicle.quv_id INNER JOIN quote ON quote.quo_id = quote_vehicle.quo_id WHERE quote.opp_id = opportunity.opp_id ORDER BY risk_study_quote_vehicle.rst_id DESC LIMIT 0,1"'));
    })
    ->where([
        ['per_id_process', 5],
        ['opp_locked', 1],
    ])
    ->get();

解决方法 #

错误原因 #

你在DB::raw中给子查询套了双引号，导致整个子查询被当成字符串常量，而非可执行的SQL语句，无法正确匹配risk_study.rst_id，因此拿不到关联数据。

修正方案1：修复DB::raw写法 #

去掉子查询外层的双引号，让Laravel正确解析执行子查询：

$opportunities = Opportunity::select('opp_id', 'risk_study.rst_id', 'risk_study.rst_status')
    ->leftJoin('risk_study', function ($join) {
        $join->on('risk_study.rst_id', '=', DB::raw('(SELECT risk_study_quote_vehicle.rst_id FROM risk_study_quote_vehicle INNER JOIN quote_vehicle ON quote_vehicle.quv_id = risk_study_quote_vehicle.quv_id INNER JOIN quote ON quote.quo_id = quote_vehicle.quo_id WHERE quote.opp_id = opportunity.opp_id ORDER BY risk_study_quote_vehicle.rst_id DESC LIMIT 1)'));
    })
    ->where('per_id_process', 5)
    ->where('opp_locked', 1)
    ->get();

修正方案2：用Laravel子查询构建器（更优雅） #

避免直接写原生SQL字符串，用Query Builder方式构建子查询，可读性和可维护性更好：

// 构建子查询
$subquery = DB::table('risk_study_quote_vehicle')
    ->select('risk_study_quote_vehicle.rst_id')
    ->join('quote_vehicle', 'quote_vehicle.quv_id', '=', 'risk_study_quote_vehicle.quv_id')
    ->join('quote', 'quote.quo_id', '=', 'quote_vehicle.quo_id')
    ->whereColumn('quote.opp_id', 'opportunity.opp_id')
    ->orderByDesc('risk_study_quote_vehicle.rst_id')
    ->limit(1);

// 主查询
$opportunities = Opportunity::select('opp_id', 'risk_study.rst_id', 'risk_study.rst_status')
    ->leftJoin('risk_study', 'risk_study.rst_id', '=', $subquery)
    ->where('per_id_process', 5)
    ->where('opp_locked', 1)
    ->get();

内容的提问来源于stack exchange，提问作者Mika