CODEWITHSUNDEEP
laraveleloquentlaravel-5.3
Stephen H. Anderson
Stephen H. Anderson

Reputation: 1068

Where clause to filter rows from three related tables using the Eloquent ORM in Laravel

We have three related models: Event, User and EventType.

Model Event has one User and also has one EventType.

Now, we are implementing a table (UI) that can be filtered from the web browser by the user. The user can filter by event type, username (that generated the event) and also by other fields like IP address.

The IP address is stored in the Events table, but the Events table has two foreign keys, user_id and event_type_id. So how can I filter using eloquent only events that were generated by a certain user and have a certain event_type?

So far, we used one query to retrieve all events that have a certain name and all users that meet the username search criteria.

Then built a third query using the whereIn clause to filter by user_id and event_type_id.

However, that's three queries.

Upvotes: 0

Views: 806

Answers (1)

Desh901
Desh901

Reputation: 2723

Try the following:

Event::whereHas('user', static function($query){

   $query->where('users.name', 'Some Value');

})->whereHas('eventType', static function($query) {

   $query->where('event_types.field', '!=', 'Some other value');

})->where('ip', 'localhost)->get();

assuming that relationships in Event model with User is called user() and with EventType is called eventType(). Also in the nested whereHas query i'm assuming that the User table is called users and the EventType table is called event_types. Change them accordingly with your actual values.

But whereHas method is actually performing a query, so the number of queries will not decrease and it will still be 3. In order to decrease the number of queries join is the way to go:

Event::select('events.*)->join('users as u', 'events.user_id', 'u.id')
->join('event_types as et', 'events.event_type_id', 'et.id')
->where('u.username', 'like', "%$search%")
->where('et.slug', 'user.created')
->where('ip', 'localhost');

Upvotes: 1

Related Questions