如何在Laravel中查询addSelect创建的字段？

我用Laravel编写了如下查询语句，通过addSelect新增了submission_date、resubmission_date、error_date三个字段，这些字段能正常在数据表格显示，但当我尝试通过submission_date做范围查询时，报错：

1054 Unknown Column submission_date.

原始查询代码：

DB::connection('mysql_slave')
    ->table('applications')
    ->whereNull('applications.deleted_at')
    ->when($column != 'contract_return_date' && $column != 'contract_delivery_date',function ($query) use ($column,$date_from,$date_to){
        return $query->whereBetween('applications.'.$column, [$date_from, $date_to]);
    })
    ->join('customers','applications.customer_id','=','customers.id')
    ->join('departments','applications.department_id','=','departments.id')
    ->select([
        'applications.id',
        'applications.customer_id',
        DB::raw('CONCAT(IFNULL(customers.last_name,"")," ",customers.first_name ) as customers_name'),
        DB::raw('CONCAT(IFNULL(applications.last_name,"")," ",applications.first_name ) as contract_name'),
        'applications.offer_type as offer_type',
        'applications.status_id',
        'applications.contract_no',
        'applications.current_provider',
        'applications.extra_offer',
        'applications.offer_warranty',
        'applications.department_id',               
        'customers.mobile_phone as customer_mobile',
        'applications.program as program',
        'applications.saled_by_text as saler',
        'departments.name as department',
        'applications.created_at as created_at',
        'applications.created_at as saled_at',
        DB::raw('IF(applications.sale=1,"NAI","OXI") as sale'),
    ])

    ->addSelect(['submission_date'=> StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->where('status','=',1)
        ->latest()
        ->take(1)
    ])

    ->addSelect(['resubmission_date'=> StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->where('status','=',2)
        ->latest()
        ->take(1)
    ])
    ->addSelect(['error_date' => StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->whereIn('status', [5, 6])
        ->latest()
        ->take(1)
    ]) ->when($column == 'contract_delivery_date',function ($query) use ($date_from,$date_to){
        return $query->whereBetween('submission_date', [$date_from, $date_to]);
    });

出现这个错误的核心原因是：MySQL的执行顺序中，WHERE子句的执行早于SELECT子句，因此无法直接在WHERE里引用SELECT（包括addSelect）中定义的字段别名。以下是两种可行的解决方法：

方法1：直接在WHERE条件中复用子查询逻辑 #

把submission_date对应的子查询直接写到whereBetween的第一个参数里，替换原来的字段别名：

->when($column == 'contract_delivery_date', function ($query) use ($date_from, $date_to) {
    return $query->whereBetween(
        // 复用submission_date的子查询逻辑
        StatusLog::select('created_at')
            ->whereColumn('application_id', 'applications.id')
            ->where('status', '=', 1)
            ->latest()
            ->take(1),
        [$date_from, $date_to]
    );
});

这种写法简单直接，适合只在单个地方使用该字段查询的场景，但如果多个地方需要用到这个逻辑，会出现代码重复。

方法2：使用子查询/CTE先构建完整数据集，再在外层查询 #

先通过子查询生成包含所有字段（包括submission_date）的临时数据集，再在外层查询中对这个临时数据集的字段做条件筛选：

// 第一步：构建包含所有字段的子查询
$subQuery = DB::connection('mysql_slave')
    ->table('applications')
    ->whereNull('applications.deleted_at')
    ->when($column != 'contract_return_date' && $column != 'contract_delivery_date', function ($query) use ($column,$date_from,$date_to){
        return $query->whereBetween('applications.'.$column, [$date_from, $date_to]);
    })
    ->join('customers','applications.customer_id','=','customers.id')
    ->join('departments','applications.department_id','=','departments.id')
    ->select([
        'applications.id',
        'applications.customer_id',
        DB::raw('CONCAT(IFNULL(customers.last_name,"")," ",customers.first_name ) as customers_name'),
        DB::raw('CONCAT(IFNULL(applications.last_name,"")," ",applications.first_name ) as contract_name'),
        'applications.offer_type as offer_type',
        'applications.status_id',
        'applications.contract_no',
        'applications.current_provider',
        'applications.extra_offer',
        'applications.offer_warranty',
        'applications.department_id',               
        'customers.mobile_phone as customer_mobile',
        'applications.program as program',
        'applications.saled_by_text as saler',
        'departments.name as department',
        'applications.created_at as created_at',
        'applications.created_at as saled_at',
        DB::raw('IF(applications.sale=1,"NAI","OXI") as sale'),
    ])
    ->addSelect(['submission_date'=> StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->where('status','=',1)
        ->latest()
        ->take(1)
    ])
    ->addSelect(['resubmission_date'=> StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->where('status','=',2)
        ->latest()
        ->take(1)
    ])
    ->addSelect(['error_date' => StatusLog::select('created_at')
        ->whereColumn('application_id','applications.id')
        ->whereIn('status', [5, 6])
        ->latest()
        ->take(1)
    ]);

// 第二步：基于子查询做外层查询，处理contract_delivery_date的条件
$query = DB::table($subQuery, 'sub')
    ->when($column == 'contract_delivery_date', function ($q) use ($date_from, $date_to) {
        return $q->whereBetween('sub.submission_date', [$date_from, $date_to]);
    });

// 获取最终结果
$results = $query->get();

这种方法逻辑更清晰，避免了代码重复，适合复杂查询场景，后续如果要对其他新增字段做查询，也可以直接在外层处理。

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