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-07 21:39:01Z

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 7, 2014 at 21:39

answered Oct 7, 2014 at 20:28

user4097807

Sign up to request clarification or add additional context in comments.

15 Comments

GRowing Over a year ago

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.

user4097807 Over a year ago

If that has provided a solution to you please accept it as an answer.

GRowing Over a year ago

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.

user4097807 Over a year ago

It only gets the first record.

GRowing Over a year ago

I need only the first record. The solution you posted throws errors,,

|

Community · Accepted Answer · 2017-05-23 12:34:24Z

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

Collectives™ on Stack Overflow

Laravel 4.2 - Eloquent WHERE clause with OR and reverse parameters

2 Answers 2

15 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

Comments

Linked

Related