Laravel: Query builder builder returning different result than actual mysql query

Question

i have table with a column name firmware_flash_result which is either fail or pass. the Query builder is returning correct result for Pass but for failure it is returning wrong result.

Actual Query and result:

 SELECT COUNT(DISTINCT `payment_module_id`) 
 FROM `payment_prod_pograms` 
 WHERE `created_at` >= "2018-09-17 00:00:00" AND 
       (
        `payment_module_id` LIKE "PB10____18____" OR 
        `payment_module_id` LIKE "PM10____18____" 
       ) AND 
       CHAR_LENGTH(`payment_module_id`) = 14 AND 
       `firmware_flash_result` = "FAIL";

+-------------------------------------+
| COUNT(DISTINCT `payment_module_id`) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

Laravel Query Builder:

$payment_prod_failed_test = DB::table('payment_prod_pograms')
        ->where('created_at','>=','2018-09-17 00:00:00')
        ->whereRaw('char_length(payment_module_id) = 14')
        ->where('payment_module_id','like','%PB10____18____%')
        ->orWhere('payment_module_id','like','%PM10____18____%')
        ->where('firmware_flash_result','=','FAIL')
        ->distinct()
        ->count('payment_module_id');

the above query is returning result 453 which is not possible.

After some debugging i found that:

->orWhere('payment_module_id','like','%PM10____18____%')

is causing this error why is it?

there are 1515 entries in database and all of them are pass but this query is returning different result.. What i am doing wrong here ? Any help will be appreciated

You have multiple condition and one of them is orWhere('payment_module_id','like','%PM10____18____%') and I guess it is not wrapped with () as in your SQL example but you get query: — Lukasz Szozda
– Lukasz Szozda, Commented Sep 29, 2018 at 9:37
They are not the same: SELECT COUNT(DISTINCT payment_module_id) FROM payment_prod_pograms where created_at > '2018-09-17 00:00:00' AND payment_module_id LIKE '%PB10____18____%' OR payment_module_id LIKE '%PM10____18____%' AND CHAR_LENGTH(payment_module_id) = 14 AND firmware_flash_result = 'FAIL'; Your ORM version is lacking () around OR condition plus missing % — Lukasz Szozda
– Lukasz Szozda, Commented Sep 29, 2018 at 9:38
@LukaszSzozda i changed it but still same result.i guess order does'nt matter — Salman Zafar
– Salman Zafar, Commented Sep 29, 2018 at 9:40

Simon R · Accepted Answer · 2018-09-29 11:02:40Z

You are grouping your where payment_module_id to be like %PB10____18____% or %PM10____18____% so you need to do the same in your eloquent query. This is done by using a closure.

$payment_prod_failed_test = DB::table('payment_prod_pograms')
        ->where('created_at','>=','2018-09-17 00:00:00')
        ->whereRaw('char_length(payment_module_id) = 14')
        ->where(function($query) { 
            $query->where('payment_module_id','like','%PB10____18____%')
                  ->orWhere('payment_module_id','like','%PM10____18____%');
        })
        ->where('firmware_flash_result','=','FAIL')
        ->distinct()
        ->count('payment_module_id');

Collectives™ on Stack Overflow

Laravel: Query builder builder returning different result than actual mysql query

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related