Laravel eloquent SQL query with OR and AND with where clause

Question

I know that sql AND can be achieved by consecutive where clauses. and OR with where(condition,'OR'). I want to choose all the messages from message table where sentTo = editor_id AND sentFrom= currentUser_id OR where sentTo = currentUser_id AND sentFrom = editor_id. I have this query which I am trying to get the result through,

    $this->data['messages'] = Message::where(function ($query) use($uId) {
        $query->where('sentTo', '=', $this->data['currentUser']->id)
            ->orWhere('sentFrom', '=', $uId);
            })
    ->where(function ($query) use($uId){
             $query->where('sentTo', '=', $uId)
                     ->orWhere('sentFrom', '=', $this->data['currentUser']->id);
            })
    ->get();

How can I do this? Please advice.

Javi Stolz · Accepted Answer · 2014-07-02 19:43:33Z

6

I'm a bit confuse as you didn't provide parentheses to know which operator precedence you want.

Assuming you want WHERE (editor_id=$editorId AND sentFrom=$currentUserId) OR (sentTo=$currentUserId AND sentFrom=$editorId) then latest version of Laravel allows you to do:

    Message::where(['editor_id' => $editorId, 'sentFrom' => $currentUserId])
    ->orWhere(['sentTo' => $currentUserId, 'sentFrom' => $editorId])->get();

No need to use closures.

answered Jul 2, 2014 at 19:43

Javi Stolz

4,7531 gold badge32 silver badges27 bronze badges

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

3 Comments

Priyanka Over a year ago

Wow! I did not know it can be done this way. Exactly what I was looking for. Thanks

GRowing Over a year ago

This is great. I used something like this myself with Laravel 4.2

Haring10 Over a year ago

over a year later and this is still the best answer on the internet. Works in 5.1

Issam Zoli · Accepted Answer · 2014-07-02 19:37:03Z

0

Try this

$this->data['messages'] = Message::where(function ($query) use($uId) {
    $query->where('sentTo', '=', $this->data['currentUser']->id)
          ->where('sentFrom', '=', $uId);
})-> orWhere(function ($query) use($uId){
    $query->where('sentTo', '=', $uId)
          ->Where('sentFrom', '=', $this->data['currentUser']->id);
})->get();

edited Jul 2, 2014 at 19:37

answered Jul 2, 2014 at 19:31

Issam Zoli

2,7741 gold badge24 silver badges36 bronze badges

3 Comments

Priyanka Over a year ago

Thanks for the efforts, but I am getting currentUser object from _construct() function in the controller. So It is available everywhere. However, your solution won't work as you are passing an object as an id. Also, if the currentUser->id was not in scope I would have got an undefined error, which I am not getting. All I get in the result is Null. Thanks

Issam Zoli Over a year ago

I think the problem is caused by $this make sure that you have the $this->data['currentUser']->id before doing the request.

Priyanka Over a year ago

I got it worked,

$this->data['messages'] = Message::where(function ($query) use($uId) {             $query->where('sentTo', '=', $this->data['currentUser']->id)                 ->Where('sentFrom', '=', $uId);                 })         ->orwhere(function ($query) use($uId){                  $query->where('sentTo', '=', $uId)                          ->Where('sentFrom', '=', $this->data['currentUser']->id);                 })         ->get();

Thanks for your efforts @Issam

Collectives™ on Stack Overflow

Laravel eloquent SQL query with OR and AND with where clause

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related