Jan 3, 2021

Level 4

How to do a query in Laravel

I have been unable to find an answer in the Laravel docs. I am using Laravel 8.

I have a conversations table with two fields that I want to check: sender_id and receiver_id.

Table:

Schema::create('conversations', function (Blueprint $table) {
            $table->id();
            $table->integer('sender_id');
            $table->integer('receiver_id');
            $table->timestamps();
});

I want to find out if a conversation between two users already exists. Since a user can be a sender or a receiver, the query must check using AND and OR.

Here's the query in SQL:

SELECT * 
FROM `conversations` 
WHERE sender_id = 3 AND receiver_id = 6 
OR receiver_id = 6 AND sender_id = 3;

I would appreciate any assistance.

kevinbui

5 years ago

Best Answer

Level 41

Lets make some parentheses:

SELECT * 
FROM `conversations` 
WHERE (sender_id = 3 AND receiver_id = 6)
OR (receiver_id = 6 AND sender_id = 3);

Or writing a query in the code:

Conversation::where(function ($query) use ($request) {
    $query->where('sender_id', $request->input('sender_id'))->where('receiver_id', $request->input('receiver_id'));
})->orWhere(function ($query) use ($request) {
    $query->where('sender_id', $request->input('receiver_id'))->where('receiver_id', $request->input('sender_id'));
})
->get();

jimb814

5 years ago

Level 4

Thanks @kevinbui. I'm getting the sender_id and receiver_id values from a form submission and attempting to use $request-> to pass them, but it's throwing an error that request is not defined. I passed it in the function to see if that works, but it threw another error. Apparently, it's not being recognized inside the function. Any idea why?

$conversation = Conversation::where(function ($query) {
            $query->where('sender_id', $request->sender_id)->where('receiver_id', $request->receiver_id);
        })->orWhere(function ($query) {
            $query->where('sender_id', $request->receiver_id)->where('receiver_id', $request->sender_id);
        });

fylzero

5 years ago

Level 67

@jimb814 Try:

DB::table('conversations')
    ->where(function($query) {
        $query->where('sender_id', 3)
            ->where('receiver_id', 6)
    })
    ->orWhere(function($query) {
        $query->where('receiver_id', 3)
            ->where('sender_id', 6)
    })
    ->get();

1 like

jimb814

5 years ago

Level 4

Thank @fylzero. I modified this to include $request based on @kevinbui's modification of his code, but it is still throwing an error:

syntax error, unexpected '}'

The code looks correct to me, however. Any idea what might be incorrect?

$conversation = DB::table('conversations')
            ->where(function($query) use ($request) {
                $query->where('sender_id', $request->sender_id)
                    ->where('receiver_id', $request->receiver_id)
            })
            ->orWhere(function($query) use ($request) {
                $query->where('receiver_id', $request->sender_id)
                    ->where('sender_id', $request->receiver_id)
            })
            ->get();

kevinbui

5 years ago

Level 41

@jimb814 I have updated my answer accordingly above. Pls use the use keyword to include outside objects to be used in a closure.

kevinbui

5 years ago

Level 41

@jimb814 You miss the semicolon ; inside each closure.

jimb814

5 years ago

Level 4

Do you mean like this:

 $conversation = DB::table('conversations')
            ->where(function($query) use ($request) {
                $query->where('sender_id', $request->sender_id)
                    ->where('receiver_id', $request->receiver_id)
            });
            ->orWhere(function($query) use ($request) {
                $query->where('receiver_id', $request->sender_id)
                    ->where('sender_id', $request->receiver_id)
            });
            ->get();

1 like

jimb814

5 years ago

Level 4

This works:

$sender_id = $request->sender_id;
$receiver_id = $request->receiver_id;

$conversation = Conversation::where(function ($query) use ($sender_id, $receiver_id) {
            $query->where('sender_id', $sender_id,)->where('receiver_id', $receiver_id);
        })->orWhere(function ($query) use ($sender_id, $receiver_id) {
            $query->where('sender_id', $receiver_id)->where('receiver_id', $sender_id);
        })
        ->get();

1 like

Please or to participate in this conversation.