Laravel Eloquent giving different result from MySQL / MariaDB query when using "WHERE CONCAT"

Question

I have a laravel eloquent query that is giving me a different result from the same query that I try at MariaDB

Eloquent:

$invoice = PurchaseInvoice::select("purchase_invoices.*", "purchase_contracts.contract_no", "partners.name as partner_name")
                    ->join("purchase_contracts","purchase_contracts.id","purchase_invoices.purchase_contract_id")
                    ->join("partners","partners.id","purchase_contracts.partner_id")
                    ->join("commodity_po","purchase_contracts.id","commodity_po.purchase_contract_id")
                    ->where("purchase_invoices.invoice_no", 'LIKE', '%'.$request->q.'%')
                    ->orWhere("purchase_invoices.po_additional_no", 'LIKE', '%'.$request->q.'%')
                    ->orWhere("purchase_contracts.contract_no", 'LIKE', '%'.$request->q.'%')
                    ->orWhereRaw("(CONCAT(`contract_no`, `po_additional_no`) LIKE '%?%')", [$request->q])->get()

and it will generate query like this :

select `purchase_invoices`.*, `purchase_contracts`.`contract_no`, `partners`.`name` as `partner_name` from `purchase_invoices` inner join `purchase_contracts` on `purchase_contracts`.`id` = `purchase_invoices`.`purchase_contract_id` inner join `partners` on `partners`.`id` = `purchase_contracts`.`partner_id` inner join `commodity_po` on `purchase_contracts`.`id` = `commodity_po`.`purchase_contract_id` where (purchase_invoices.invoice_no LIKE '%SLP0622/6A-%' or purchase_invoices.po_additional_no LIKE '%SLP0622/6A-%' or purchase_contracts.contract_no LIKE '%SLP0622/6A-%' or (CONCAT(`contract_no`, `po_additional_no`) LIKE '%SLP0622/6A-%')) and `purchase_invoices`.`deleted_at` is null;

when I run the query on laravel, it give me empty result

but when I run the generated query on MySQL it give me 1 result

Does anyone know why this can happened? is this because there is a slash '/' on the query?

In orWhereRaw you are matching with exact value instead of contain. May be due to this you are geting null. * ->orWhereRaw("CONCAT(contract_no, po_additional_no)", 'like', '%'.$request->q.'%')->get()* — Waqas Altaf
– Waqas Altaf, Commented Jul 5, 2022 at 9:01

apokryfos · Accepted Answer · 2022-07-05 08:56:05Z

2

'%?%' is not a valid parameter. You need to pass it like:

->orWhereRaw("(CONCAT(`contract_no`, `po_additional_no`) LIKE ?)", [ '%'.$request->q.'%'])->get()

answered Jul 5, 2022 at 8:56

apokryfos

40.9k11 gold badges85 silver badges128 bronze badges

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

1 Comment

Miky Setiawan Over a year ago

whoa its working, thank you very much! I feel so stupid LOL

Collectives™ on Stack Overflow

Laravel Eloquent giving different result from MySQL / MariaDB query when using "WHERE CONCAT"

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