How to Use Laravel Query Builder with and Where Clause in PHP?

Question

I am trying to ensure that a record exists in the database before performing a user-related action. When I execute the query directly in my PHPMyAdmin like so (for testing purposes).

SELECT * FROM `chat_participants` WHERE `chat_id` = 2 AND `user_id` = 2

I receive the correct record. However, when I try to use the Laravel Query Builder to achieve the same.

dd($this->participants
    ->where('chat_id', '=', 2)
    ->where('user_id', '=', 2)
    ->get()
    ->first());

I get null. Is there a way I can ensure that the record exists in the database using Query Builder? Do I need to declare AND in the Query Builder?

Update: I set the participants variable in my constructor.

public function __construct()
{
    $this->middleware('auth');
    $this->header = DB::table('chat_headers');
    $this->participants = DB::table('chat_participants');
    $this->messages = DB::table('chat_messages');
}

toSql() produces:

select * from chat_participants` 
    inner join chat_headers on chat_headers.id = chat_participants.chat_id 
    inner join chat_rbac on chat_rbac.id = chat_participants.rbac 
 where chat_participants.chat_id = ? and chat_participants.user_id = ? 
    and chat_id = ? and user_id = ?

Do $this->participants->where('chat_id', '=', 2)->where('user_id', '=', 2)->toSql() to see what SQL query is being generated. Multiple where() calls are automatically ANDed together. Side note: ->get()->first() can just be ->first(). — ceejayoz
– ceejayoz, Commented Jan 29, 2019 at 20:13
->get()->first() is redundant btw; ->get() will return a Collection of records that match your query, followed by ->first() will return the first of those records. You can simply use ->first() to save some calculation time. — Tim Lewis
– Tim Lewis, Commented Jan 29, 2019 at 20:14
"select * from chat_participants` inner join chat_headers on chat_headers.id = chat_participants.chat_id inner join chat_rbac on chat_rbac.id = chat_participants.rbac where chat_participants.chat_id = ? and chat_participants.user_id = ? and chat_id = ? and user_id = ?` @ceejayoz — Jaquarh
– Jaquarh, Commented Jan 29, 2019 at 20:15

Devon Bessemer · Accepted Answer · 2019-01-29 20:27:02Z

2

Objects are passed by reference in PHP, this means you're passing a pointer to the same Query Builder object everywhere you use the participants property. So in most cases, you would want to instantiate a new query builder object for each query.

However, PHP does allow for cloning of objects, which would allow you to pass a base query builder and start fresh everywhere you use it:

$query = clone $this->participants;
$query->where(..)->first();

This is an implementation of the Prototype OO pattern, build up a base object and then create a copy of it to do the specifics.

This would be just one way you could avoid hard-coding your table name everywhere you query it.

answered Jan 29, 2019 at 20:27

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

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

1 Comment

Jaquarh Over a year ago

Thank you for this information! You realised the concept of making participants a property to save constantly writing DB::table(). I had no clue you could use the clone keyword like so!

Collectives™ on Stack Overflow

How to Use Laravel Query Builder with and Where Clause in PHP?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related