Laravel MySQL query that have 'AND' and 'OR' inside one query

Question

I have a problem while doing some SELECT query for making a "Search" method. Here is my table structure table 'users': name | varchar, username | varchar, role | enum[Admin, Owner, Cashier], address | text

I have made an ajax script that sends a parameter called query into my Laravel Controller. This query parameter contains input from the users searching for some data in my admin.blade.php page that only shows users having Admin role from the users' table. Here is my query:

$data = DB::table('users')
                        ->join('outlets','users.outlet_id','outlets.outlet_id')
                        ->where('users.role','Admin')
                        ->where('users.nama','like','%'.$query.'%')
                        ->where('users.username','like','%'.$query.'%')
                        ->orWhere('outlets.nama_outlet','like','%'.$query.'%')
                        ->orWhere('users.address','like','%'.$query.'%')
                        ->orderBy('users.user_id')
                        ->get();

You don't have to give attention to the join. The point of my problem is the query above returns also the user that has Owner and Cashier role. For example, I have an Admin named John and an Owner named Jane, and they both have the same address, like New York. When the user typed New York in the search box, the query above should return only John. But, in my case, it also returns Jane.

How must I do the query?

I hope you understand my question. Thank you.

Does this answer your question? [Nested 'AND OR' Query in Eloquent](https://stackoverflow.com/questions/25129117/nested-and-or-query-in-eloquent) — El_Vanja, Mar 21 '20 at 14:06
Yes, it does. Thanks a lot for your help @El_Vanja, sorry for my ignorance. — Dani Fadli, Mar 21 '20 at 14:35

Dilip Hirapara · Accepted Answer · 2020-03-21T14:31:29.313

1

You can use where query in the function as below.

$data = DB::table('users')
                ->join('outlets','users.outlet_id','outlets.outlet_id')
                ->where('users.role','Admin')
                ->where(function($q) use ($query) {
                    $q->where('users.nama','like','%'.$query.'%');
                    $q->orWhere('users.username','like','%'.$query.'%');
                    $q->orWhere('outlets.nama_outlet','like','%'.$query.'%');
                    $q->orWhere('users.address','like','%'.$query.'%');
                })->orderBy('users.user_id')
                ->get();

edited Mar 21 '20 at 14:31

answered Mar 21 '20 at 14:08

Dilip Hirapara

14,810
3
27
49

Why do I get `syntax error, unexpected '}'` above `->orderBy('users.user_id')`? – Dani Fadli Mar 21 '20 at 14:26
Try edited code :) – Dilip Hirapara Mar 21 '20 at 14:33
1

Ah, I see, I forget the semicolon. Thanks for your time and help @Dilip Hirapara. – Dani Fadli Mar 21 '20 at 14:34

score 1 · Answer 2 · answered Mar 21 '20 at 14:11

Try this one:

            $data = DB::table('users')
                ->join('outlets','users.outlet_id','outlets.outlet_id')
                ->where('users.role','Admin')
                ->Where(function($query) use ($name){
                  $query->where('users.nama','like','%'.$query.'%')
                    ->orWhere('users.username','like','%'.$query.'%')
                    ->orWhere('outlets.nama_outlet','like','%'.$query.'%')
                    ->orWhere('users.address','like','%'.$query.'%');
                })                       
                ->orderBy('users.user_id')
                ->get();

Your answer is similar to Dilip Hirapara's answer and it fixes my problem. But I have to make Dilip Hirapara's answer as the best answer because he answered first. But anyway, thanks a lot. — Dani Fadli, Mar 21 '20 at 14:37

Laravel MySQL query that have 'AND' and 'OR' inside one query

2 Answers2