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.

score 6 · Accepted Answer · answered Jul 02 '14 at 19:43

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 02 '14 at 19:43

Javi Stolz

4,720
1
30
27

Wow! I did not know it can be done this way. Exactly what I was looking for. Thanks – Priyanka Jul 02 '14 at 20:30
This is great. I used something like this myself with Laravel 4.2 – GRowing Oct 08 '14 at 02:41
over a year later and this is still the best answer on the internet. Works in 5.1 – Haring10 Dec 11 '15 at 05:35

Issam Zoli · Answer 2 · 2014-07-02T19:37:03.770

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 02 '14 at 19:37

answered Jul 02 '14 at 19:31

Issam Zoli

2,724
1
21
35

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 – Priyanka Jul 02 '14 at 19:36
I think the problem is caused by $this make sure that you have the $this->data['currentUser']->id before doing the request. – Issam Zoli Jul 02 '14 at 19:38
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 – Priyanka Jul 02 '14 at 19:43

Laravel eloquent SQL query with OR and AND with where clause

2 Answers2

Linked