Laravel 4.2 - Eloquent WHERE clause with OR and reverse parameters

Question

I have Eloquent statement in Laravel 4.2 that looks like this

    $user_message_block = Message::where('responder_id', '=', Auth::user()->id)
                        ->where('user_id', '=', $user->id)->first();

Then if $user_message_block doesn't exist I also have to check for a reverse case scenario and I do it like this...

    if(!$user_message_block){
        $user_message_block = Message::where('responder_id', '=', Auth::user()->id)
                            ->where('user_id', '=', $user->id)->first();
    }

What I really would like to do is run a single query that checks for both scenarios at once..

In pseudo expression I need something like this:

$user_message_block = Message::where('responder_id', '=', Auth::user()->id, 
                                 'AND', 'user_id', '=', $user->id, 
                                 'OR', 'responder_id', '=', $user->id, 
                                 'AND', 'user_id', '=', Auth::user()->id)->first();

So basically I need to

SELECT Message where (responder_id=x AND user_id=y) OR where (responder_id=y AND user_id=x)

How could I do this using Eloquent. I am unable to find more about OR and AND statements used with Eloquent.

Thanks!

UPDATE:

After more looking I found that this seems to work (still testing a lot)

$user_message_block = 
        Message::where(['responder_id' => Auth::user()->id, 'user_id' => $user->id])
             ->orWhere(['user_id' => Auth::user()->id, 'responder_id' => $user->id])
             ->first();

OR

$user_message_block = 
        Message::where(['responder_id' => Auth::user()->id, 'user_id' => $user->id])
             ->orWhere(['user_id' => Auth::user()->id, 'responder_id' => $user->id])
             ->get();

Are there any drawbacks to this that I need to consider?

score 4 · Accepted Answer · 2014-10-07T21:39:01.827

4

It may be very complicated to do in Eloquent but this does exactly what you need using Laravel's query builder without reverting to raw SQL.

    DB::table('messages')
        ->where(function($query)
        {
            $query->where('responder_id', '=', 'x')
            ->where('user_id', '=', 'y');
        })
        ->orWhere(function($query) {
            $query->where('responder_id', '=', 'y')
                  ->where('user_id', '=', 'x');
        })
        ->get();

This is what I get from running a toSql() on it:

select * from `messages` where (`responder_id` = ? and `user_id` = ?) or (`responder_id` = ? and `user_id` = ?)

Try this to see if it works with Eloquent.

Message::where(function($query) {
    $query->where('responder_id', '=', 'x')
          ->where('user_id', '=', 'y');
})->orWhere(function($query) {
    $query->where('responder_id', '=', 'y')
          ->where('user_id', '=', 'x');
})->get();

It should work.

edited Oct 07 '14 at 21:39

answered Oct 07 '14 at 20:28

mmmm,, I just found something about Eloquent that seems to work,, I'll update my question. It looks like I can pass arrays of information and use ->orWhere eloquent clause. – GRowing Oct 07 '14 at 20:30
If that has provided a solution to you please accept it as an answer. – Oct 07 '14 at 21:09
I am using a solution I posted above. I'll test this later to see if there are any apparent differences that make this solution more viable. – GRowing Oct 07 '14 at 21:26
It only gets the first record. – Oct 07 '14 at 21:28
I need only the first record. The solution you posted throws errors,, – GRowing Oct 07 '14 at 21:36
My actual expression looks like this: $user_message_block = Message::where(function($query) { $query->where('responder_id', '=', Auth::user()->id) ->where('user_id', '=', $user->id); })->orWhere(function($query) { $query->where('responder_id', '=', $user->id) ->where('user_id', '=', Auth::user()->id); })->get(); and I am getting user not defined – GRowing Oct 07 '14 at 21:42
Yes and what does this return. – Oct 07 '14 at 21:43
I updated the comment,, sorry,, I am getting user not defined,,, where as in my version of the query it is defined.. – GRowing Oct 07 '14 at 21:44
User not defined? This has nothing to do with the query itself – Oct 07 '14 at 21:46
Read here - http://stackoverflow.com/questions/16995102/laravel-4-eloquent-where-with-or-and-or this is the proper way to do advanced wheres. – Oct 07 '14 at 21:47
I have seen that post. OK.. so after testing a bit, yes your query works.. but for some reason (could be laravel/eloquent version) User model can't be accessed from within the closure in this expression. I will have to look deeper into that. Oddly,, in my version of the query User is accessible. Maybe this is a call for another question .. – GRowing Oct 07 '14 at 22:18
Where did you find that query? Never seen anything like that passing an array in a where clause. – Oct 07 '14 at 22:18
Alex from phpacademy (you can youtube it) did something similar to that in one of his laravel lessons.. AND I will try to find stack post that uses it as a solution.. – GRowing Oct 07 '14 at 22:21
Interesting. I guess both work then. Glad you were able to solve the problem regardless. – Oct 07 '14 at 22:26
here it is http://stackoverflow.com/questions/24539439/laravel-eloquent-sql-query-with-or-and-and-with-where-clause – GRowing Oct 07 '14 at 22:42

score 0 · Answer 2 · edited May 23 '17 at 12:34

I credited Noah for the solution on account of providing a working solution and the effort he made in helping me out. I did end up using a different solution however..

$user_message_block = 
    Message::where(['responder_id' => 'x', 'user_id'      => 'y'])
           ->orWhere(['user_id'    => 'x', 'responder_id' => 'y'])
           ->get();

In my speciffic case I only need the first record found so this works for me

$user_message_block = 
    Message::where(['responder_id' => 'x', 'user_id'      => 'y'])
           ->orWhere(['user_id'    => 'x', 'responder_id' => 'y'])
           ->first();

Suffice it to say,, I found a solution similar to mine in phpacademy videos by Alex. And I stumbled upon this too Laravel eloquent SQL query with OR and AND with where clause

Laravel 4.2 - Eloquent WHERE clause with OR and reverse parameters

2 Answers2