How to use AND operator in Laravel Query?

Question

Select a user

where id =! Auth::user->id 

// this prevents the query returning the current user logged in 

where Auth::user->value1 ,'=', 'value1'  ||   'value2' || 'value3'

So in english , find the a username where id is not equal to the currently logged in user id and where authenticated user's value1 column equals another user's value1 or value2 or value 3column

Below is a statement, i have done which isn't working correctly however it does work without the OR operator

where(Auth::user()->recommendationWord1, '=' , 'value1')

this statement is an example of the format and contains what I have tried.

$Query = DB::table('users')
    ->select('username')
    ->where('id', '!=', Auth::user()->id)
    ->whereIn(Auth::user()->recommendationWord1 , '=', 'value1', '||' , 'value2', '||' , 'value3'
    ->get();

Below is the code for my HTML page and how i am posting it

@foreach ($Query as $selectedUser)

    Code formatting<p> {{ $selectedUser->username}}</p>  

@endforeach

Check [here](https://stackoverflow.com/questions/29115385/how-to-make-laravel-eloquent-in-query). — Tpojka, Feb 16 '19 at 23:03
Then what @Davit left you there with change: `'recommendationWord1'` instead of `Auth::user()->recommendationWord1`. First argument of `whereIn($column_name, $array)` method is name of column you want to check array values against. Check docs. — Tpojka, Feb 16 '19 at 23:51
ok , also $array that contains column names as well just checking that you know that , im basically just comparing columns and finding those which r the same — John, Feb 17 '19 at 00:05

score 2 · Accepted Answer · edited Feb 16 '19 at 23:46

2

where in structure is ->whereIn('attribute', ['val1', 'val2', ...]) In your case

DB::table('users')
    ->select('username')
    ->where('id', '!=', Auth::user()->id)
    ->whereIn(Auth::user()->recommendationWord1 , ['value1', 'value2', 'value3'])
    ->get();

edited Feb 16 '19 at 23:46

Tpojka

6,996
2
29
39

answered Feb 16 '19 at 23:09

Davit Zeynalyan

8,418
5
30
55

Below is the error message , Column not found: 1054 Unknown column 'test1' in 'where clause' (SQL: select `username` from `users` where `id` != 7 and `test1` in (value1, value2, value3)) . the Auth::user()->recommendationWord1 returns test1 which is correct as thats what is stored in that field – John Feb 16 '19 at 23:15
It seems to not be return or getting the values from the field value1, value2 and value3 – John Feb 16 '19 at 23:18
@John argument should be name of column not value. I.e. `'recommendationWord1' ` instead of `Auth::user()->recommendationWord1`. – Tpojka Feb 16 '19 at 23:55

daenerysTarg · Answer 2 · 2019-02-16T23:31:34.190

Have you tried the grouping parameter?

$Query = DB::table('users')
        ->select('username')
        ->where('id', '!=', Auth::user()->id)
        ->where(function($query){
              $query->where(Auth::user()->recommendationWord1, '=', 'value1')
              ->orWhere(Auth::user()->recommendationWord1, '=', 'value2')
              ->orWhere(Auth::user()->recommendationWord1, '=', 'value3');
              })
         ->get();

See the example from the documentation:

Sometimes you may need to create more advanced where clauses such as "where exists" clauses or nested parameter groupings. The Laravel query builder can handle these as well. To get started, let's look at an example of grouping constraints within parenthesis:

DB::table('users')
            ->where('name', '=', 'John')
            ->where(function ($query) {
                $query->where('votes', '>', 100)
                      ->orWhere('title', '=', 'Admin');
            })
            ->get();
enter code here

As you can see, passing a Closure into the where method instructs the query builder to begin a constraint group. The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

https://laravel.com/docs/5.7/queries#where-clauses

yes this look very good , It looks a lot neat. However the main thing that I am looking is basically instead of doing Auth::user()->recommendationWord1 = word1 , word2 , word3 , and so on repeatedly — John, Feb 16 '19 at 23:32
It you look at @Davit answer below it shows it clearly but doesnt work correctly — John, Feb 16 '19 at 23:33
let me know if it worked. Not sure if you saw it i edited my answer with your query possible solution — daenerysTarg, Feb 16 '19 at 23:34
yes that solution does work but i would like it to be shortened down , thats my problem now. so like 1 orWhere statement would be great but not repeating Auth::user part — John, Feb 16 '19 at 23:45
not sure if it can be achieved..at least i dont know it..what if you tried to combine my solution with the solution of @Davit ? using whereIn instead of orWhere — daenerysTarg, Feb 17 '19 at 00:01

score 2 · Answer 3 · answered Feb 16 '19 at 23:23

2

Try this:

$users = User
            ::where('id', '<>', auth()->id())
            ->whereIn(auth()->user()->recommendationWord1, ['value1', 'value2', 'value3']) 
            ->get()

answered Feb 16 '19 at 23:23

Kenny Horna

13,485
4
44
71

"whereIn" array:3 [▼ 0 => "test1" 1 => "=" 2 => array:3 [▼ 0 => "word1" 1 => "word2" 2 => "word3" ] ] – John Feb 16 '19 at 23:36
That is the output of that statement , seems to not be getting word1 or in the example I gave value1 etc – John Feb 16 '19 at 23:36
How that is an output of this sentences? what did you `dd()` to get this result? – Kenny Horna Feb 16 '19 at 23:37
No sorry I meant to say it was in the error message console output , my bad. – John Feb 16 '19 at 23:41
I'll post in my answer how im outputting it – John Feb 16 '19 at 23:43

How to use AND operator in Laravel Query?

3 Answers3