Yii2 QueryBuilder中andFilterCompare用子查询结果无返回问题

核心问题分析 #

你遇到的无结果问题主要来自两个关键点：

1. SQL语法限制：WHERE子句不能直接引用SELECT中定义的列别名（比如order_total），因为数据库会先执行WHERE过滤数据，再执行SELECT生成别名列，此时别名还未生成。
2. andFilterCompare用法错误：这个方法的第二个参数默认会被当作值处理，你写的< order_total会被解析成字符串字面量，导致生成的SQL逻辑完全不符合预期。

具体排查步骤 #

• 先验证数据本身：去掉andFilterCompare条件执行查询，查看返回的total_paid和order_total数值，确认是否存在total_paid < order_total的记录。如果本身没有符合条件的数据，自然查不到结果。
• 查看生成的原生SQL：在调用all()前，用$Query->createCommand()->getRawSql()打印实际执行的SQL语句，检查是否有语法错误或逻辑偏差。比如你当前的写法可能生成类似(select ...) = '< order_total'的错误逻辑。
• 替换WHERE条件为HAVING：HAVING子句在SELECT之后执行，可以直接引用SELECT中的列别名，这是最简洁的修正方式。
• 重复子查询逻辑到WHERE中：如果必须用WHERE，可以把total_paid和order_total对应的子查询完整写到WHERE条件里，避免引用别名。

修正后的示例代码 #

方案1：使用HAVING（推荐） #

$Query = (new Query())
->select([
    'i.id as order_id',
    'i.uid as order_uid',
    'i.status as order_status',
    'i.created as order_created',
    'i.car_id as car_id',
    'i.user_id as user_id',
    'cc.customer_id as customer_id',
    'cc.make_id as make_id',
    'cc.model_id as model_id',
    'cc.color_id as color_id',
    'cc.plate_country as plate_country',
    'cc.plate_text as plate_text',
    'mk.title as make_title',
    'md.title as model_title',
    'c.title as customer_title',
    '(select coalesce(sum(cost),0) from moneyflow where order_uid = i.uid and type_id = 14) as total_paid',
    '(select (select coalesce(sum(cost),0) from workflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from partsflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from moneyflow where flow = -1 and type_id != 13 and order_uid = i.uid)) as order_total'
])
->from('orders i')
->leftJoin('customer_cars cc','i.car_id = cc.id')
->leftJoin('car_makes mk','cc.make_id = mk.id')
->leftJoin('car_models md','cc.model_id = md.id')
->leftJoin('customers c','cc.customer_id = c.id')
->where(['i.status' => 2])
->having(['<', 'total_paid', new \yii\db\Expression('order_total')])
->all();

方案2：在WHERE中重复子查询逻辑 #

$Query = (new Query())
->select([
    // 保持原SELECT字段不变
    'i.id as order_id',
    'i.uid as order_uid',
    'i.status as order_status',
    'i.created as order_created',
    'i.car_id as car_id',
    'i.user_id as user_id',
    'cc.customer_id as customer_id',
    'cc.make_id as make_id',
    'cc.model_id as model_id',
    'cc.color_id as color_id',
    'cc.plate_country as plate_country',
    'cc.plate_text as plate_text',
    'mk.title as make_title',
    'md.title as model_title',
    'c.title as customer_title',
    '(select coalesce(sum(cost),0) from moneyflow where order_uid = i.uid and type_id = 14) as total_paid',
    '(select (select coalesce(sum(cost),0) from workflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from partsflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from moneyflow where flow = -1 and type_id != 13 and order_uid = i.uid)) as order_total'
])
->from('orders i')
->leftJoin('customer_cars cc','i.car_id = cc.id')
->leftJoin('car_makes mk','cc.make_id = mk.id')
->leftJoin('car_models md','cc.model_id = md.id')
->leftJoin('customers c','cc.customer_id = c.id')
->where(['i.status' => 2])
->andWhere(new \yii\db\Expression('
    (select coalesce(sum(cost),0) from moneyflow where order_uid = i.uid and type_id = 14) 
    < 
    (select (select coalesce(sum(cost),0) from workflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from partsflow where order_uid = i.uid) + (select coalesce(sum(cost),0) from moneyflow where flow = -1 and type_id != 13 and order_uid = i.uid))
'))
->all();

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