语句不返回结果，但sql语句返回结果。

Question

Laravel查询生成器

     $data = CustomerPrepaid
            ::join('pos_sales', 'customer_prepaid.customer_id', '=', 'pos_sales.customer_id')
            ->join('pos_sales_product', 'pos_sales.pos_sales_code', '=', 'pos_sales_product.pos_sales_code')
            ->where('pos_sales_product.product_id', 'customer_prepaid.product_id')
            ->select('customer_prepaid.customer_id', 'customer_prepaid.created_at',
            'pos_sales_product.pos_sales_product_code as reference_no', 

'customer_prepaid.product_id', 'customer_prepaid.balance', 
        'last_used', 'expiry_date', 'customer_prepaid.amount as price')
        ->offset(($page-1)*$limit)->limit($limit)->get();

SQL

SELECT customer_prepaid.customer_id, customer_prepaid.created_at as purchase_date,
pos_sales_product.pos_sales_product_code as reference_no, customer_prepaid.product_id, 
customer_prepaid.balance, customer_prepaid.amount*customer_prepaid.balance as value, 
last_used, expiry_date, customer_prepaid.amount as price,
customer_prepaid.amount*customer_prepaid.balance as total
FROM customer_prepaid
JOIN pos_sales ON customer_prepaid.customer_id = pos_sales.customer_id
JOIN pos_sales_product ON pos_sales.pos_sales_code = pos_sales_product.pos_sales_code
WHERE pos_sales_product.product_id = customer_prepaid.product_id 

在服务器上执行的结果SQL返回正确的结果，但我没有得到令人信服的结果，为什么会这样呢？

Answer 1

天啊，我花了很长时间才意识到你滥用了->where。

将->where更改为->whereColumn

$data = CustomerPrepaid
        ::join('pos_sales', 'customer_prepaid.customer_id', '=', 'pos_sales.customer_id')
        ->join('pos_sales_product', 'pos_sales.pos_sales_code', '=', 'pos_sales_product.pos_sales_code')
        ->whereColumn('pos_sales_product.product_id', 'customer_prepaid.product_id')
        ->select(
            'customer_prepaid.customer_id',
            'customer_prepaid.created_at',
            'pos_sales_product.pos_sales_product_code as reference_no',
            'customer_prepaid.product_id', 'customer_prepaid.balance',
            'last_used', 'expiry_date', 'customer_prepaid.amount as price'
        )
        ->offset(($page-1)*$limit)
        ->limit($limit)
        ->get();

您必须使用whereColumn而不是where来进行列比较。否则，它将期望设置第三个参数value。

查看有关如何使用whereColumn：https://laravel.com/docs/5.5/queries#where-clauses的文档