MySQL转Laravel实现右连接与子查询计数时报错的技术咨询

Let's break down why you're hitting this error and fix it to match your original MySQL query.

The Root Cause #

Your original SQL uses a correlated subquery to calculate c_count, but your Laravel code tries to directly reference carinraceseries.crs_id in the select list without either joining the carinraceseries table or using a subquery. Since you haven't joined that table, MySQL throws the "unknown column" error.

Solution 1: Use DB::raw() to Replicate the Original Subquery #

This approach directly mirrors your original SQL structure within Laravel's query builder:

DB::table('raceseriesmap')
    ->select([
        'raceseriesmap.rs_id',
        'raceseriesmap.m_id',
        'raceseriesmap.rsm_mo',
        'raceseriesmap.rsm_extra',
        'mapdata.m_name',
        'mapdata.m_photoname',
        // Embed the original subquery using DB::raw()
        DB::raw('(SELECT count(carinraceseries.crs_id) FROM carinraceseries WHERE carinraceseries.rs_id = raceseriesmap.rs_id) AS c_count')
    ])
    ->rightJoin('mapdata', 'mapdata.m_id', '=', 'raceseriesmap.m_id')
    ->where('raceseriesmap.m_id', '=', 4)
    ->get();

Solution 2: Use Laravel's Subquery Builder (More "Laravel-Style") #

If you prefer to avoid raw SQL strings, you can build the subquery as a separate query instance:

// First define the subquery for counting
$countSubquery = DB::table('carinraceseries')
    ->selectRaw('count(crs_id)')
    ->whereColumn('rs_id', 'raceseriesmap.rs_id');

// Build the main query
DB::table('raceseriesmap')
    ->select([
        'raceseriesmap.rs_id',
        'raceseriesmap.m_id',
        'raceseriesmap.rsm_mo',
        'raceseriesmap.rsm_extra',
        'mapdata.m_name',
        'mapdata.m_photoname',
        // Attach the subquery and alias it as c_count
        $countSubquery->as('c_count')
    ])
    ->rightJoin('mapdata', 'mapdata.m_id', '=', 'raceseriesmap.m_id')
    ->where('raceseriesmap.m_id', 4)
    ->get();

What This Fixes #

Both solutions will generate SQL that matches your original query exactly, including the correlated subquery for c_count. This resolves the "column not found" error because we're no longer trying to reference carinraceseries columns without a proper subquery context.

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