Laravel 10中字段不等于查询失效问题求助

问题描述 #

在Laravel 10.0版本中，使用Eloquent构建查询时，尝试通过where方法筛选invoices.itemTotalPrice不等于invoices.paymentCollected的数据，但该条件未生效——查询返回了指定日期范围内的所有数据，未排除两个字段值相等的行。但生成的SQL语句直接在MySQL命令行执行时，能得到正确的筛选结果。

原代码及生成SQL #

Eloquent代码 #

Invoice::where('invoices.centre_code', $userInfo->centre_code)
                    ->where("invoices.itemTotalPrice","<>", "invoices.paymentCollected") // 此行不生效
                    ->join('customers', 'customers.customer_code', '=', 'invoices.customerCode')
                    ->join('items', 'items.id', '=', 'invoices.itemId')
                    ->select('invoices.*', 'customers.customerName', 'customers.customerDialCode', 'customers.customerCity', 'customers.customerAddress', 'customers.customerMobile', 'items.itemName')
                    ->whereBetween('invoices.created_at', [$from, $to])
                    ->oldest("invoices.nextPaymentDate")
                    ->get();

生成的SQL语句 #

select `invoices`.*, `customers`.`customerName`, `customers`.`customerDialCode`, `customers`.`customerCity`, `customers`.`customerAddress`, `customers`.`customerMobile`, `items`.`itemName` from `invoices` inner join `customers` on `customers`.`customer_code` = `invoices`.`customerCode` inner join `items` on `items`.`id` = `invoices`.`itemId` where `invoices`.`centre_code` = 705405 and `invoices.itemTotalPrice` <> invoices.paymentCollected and `invoices`.`created_at` between 2023-03-24 00:00:00 and 2023-03-24 23:59:59 order by `invoices`.`nextPaymentDate` asc

原因分析 #

问题出在where("invoices.itemTotalPrice","<>", "invoices.paymentCollected")这一行：Laravel的where方法默认会将第三个参数当作字面量字符串处理，而非数据库字段名。也就是说，实际执行时是把invoices.itemTotalPrice的值和字符串"invoices.paymentCollected"做比较，而不是和invoices.paymentCollected字段的值比较，这就导致筛选条件完全不符合预期。

解决方案 #

使用Laravel提供的whereColumn方法，该方法专门用于字段与字段之间的比较，支持所有常用的比较运算符。

修正后的Eloquent代码 #

Invoice::where('invoices.centre_code', $userInfo->centre_code)
                    ->whereColumn("invoices.itemTotalPrice","<>", "invoices.paymentCollected") // 替换为whereColumn
                    ->join('customers', 'customers.customer_code', '=', 'invoices.customerCode')
                    ->join('items', 'items.id', '=', 'invoices.itemId')
                    ->select('invoices.*', 'customers.customerName', 'customers.customerDialCode', 'customers.customerCity', 'customers.customerAddress', 'customers.customerMobile', 'items.itemName')
                    ->whereBetween('invoices.created_at', [$from, $to])
                    ->oldest("invoices.nextPaymentDate")
                    ->get();

补充说明 #

whereColumn还支持同时比较多组字段，例如：

->whereColumn([
    ['invoices.field1', '=', 'invoices.field2'],
    ['invoices.field3', '>', 'invoices.field4']
])

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