Laravel query Or operator priority

Question

I am using laravel 4 query. I want get data by OR condition. Is there any priority rule with Laravel 4 ?

My Query is :

SELECT * 
FROM order_items 
WHERE (status = 'Y' OR (status = 'N' AND amount > 100)) 
AND product_id = '15' 
LIMIT 1;

I have 2 order items but i want the first item in the result will get the prority status = 'Y', if its not there, then it meet the second condition of OR.

Here is my laravel code :

$sql_select = "SELECT * 
               FROM order_items 
                WHERE (status = 'Y' OR (status = 'N' AND amount > 100)) 
                AND product_id = '15' 
                LIMIT 1";
$data = DB::select($sql_select);
echo "<pre>";
print_r($data);
die;

For simple mysql query, its working, but in laravel its not giving results.

Also `LIMIT 1` without `ORDER BY` does not make sense as SQL tables/resultsets are by SQL standards definition **orderless** — Raymond Nijland, Jul 01 '19 at 12:36
Possible duplicate of [How to Create Multiple Where Clause Query Using Laravel Eloquent?](https://stackoverflow.com/questions/19325312/how-to-create-multiple-where-clause-query-using-laravel-eloquent) — nice_dev, Jul 01 '19 at 12:39
Considering you're passing in the exact same query without using the query builder, it should return the same results. — aynber, Jul 01 '19 at 12:40
@RaymondNijland i dont want `ORDER BY` , i want to order by `status `. And dont need ->get() here — Maha Dev, Jul 01 '19 at 12:45
*"i dont want ORDER BY , i want to order by status"* you don't order by nothing now so MySQL is free to return anny record which matches the WHERE to be returned this can be the first, second or third record match and so on i hope you understand mine point here.. — Raymond Nijland, Jul 01 '19 at 12:47

score 3 · Answer 1 · answered Jul 01 '19 at 12:51

3

Maybe you should try this way

DB::table('order_items')
        ->where('status', 'Y')
        ->orWhere(function($query)
        {
            $query->where('status', 'N')
                  ->where('amount', '>', '100');
        })
    ->where('product_id', 15)
        ->first();

answered Jul 01 '19 at 12:51

andreeab

81
3

Not sure how laraval implemented `first()` so iam unsure if this any better then using `LIMIT 1` without `ORDER BY` clause.. But i assume this is implemented like a dirty/lazy first array element selection on database results for some reason.. – Raymond Nijland Jul 01 '19 at 13:17
Method first() is retrieving the first model matching the query constraints – andreeab Jul 02 '19 at 08:55

Berzohr · Answer 2 · 2021-09-19T19:55:59.363

0

Great solution andreeab.

In my case the SQL query is:

SELECT ... WHERE c1 = 'xyz' AND (c2 = 100 OR c2 = 200);

I wrote in Eloquent:

 DB:table('tablename')
    ->where('c1', '=', 'xyz')
    ->where(function($query)
    {
       $query->where('c2', '=', 100)
             ->orWhere('c2', '=', 200); 
    })
    ->get();

edited Sep 19 '21 at 19:55

answered Aug 28 '21 at 22:36

Berzohr

306
3
16

Laravel query Or operator priority

2 Answers2