Laravel Query Builder Statement return no result, but sql statement does

Question

Laravel Query Builder

     $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

The resulting SQL executed on the server returns the right result, but I get no eloquent result, why might that be?

Did you check the SQL generated by the Query Builder using ->toSql() instead of ->offset(($pag..... ? — Amarnasan
– Amarnasan, Commented Jan 22, 2018 at 8:34
After checked with toSql(),I get this result. puu.sh/z70cw/4a4cddc975.png — Nay Kyi
– Nay Kyi, Commented Jan 22, 2018 at 8:40
Could it be $page is giving you paged result? Have you tried the query builder without offset and limit? — Lionel Chan
– Lionel Chan, Commented Jan 22, 2018 at 8:55

Lionel Chan · Accepted Answer · 2018-01-22 09:29:25Z

1

Oh gosh, took me forever to realize you misused ->where.

Change your ->where to ->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();

You have to use whereColumn instead of where to make column comparison. Else it's expecting a third parameter value to be set.

Check the documentation on how to use whereColumn: https://laravel.com/docs/5.5/queries#where-clauses

answered Jan 22, 2018 at 9:29

Lionel Chan

8,0795 gold badges43 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nay Kyi Over a year ago

Thanks you sir. like that ok :)

Collectives™ on Stack Overflow

Laravel Query Builder Statement return no result, but sql statement does

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related