Eloquent Relation Filtering

Question

Is it possible to filter a result set in Laravel's Eloquent ORM by related models? I understand that Eloquent does not join tables but the results I would like would be similar to:

SELECT * FROM tickets JOIN statuses on tickets.status_id = statuses.id WHERE statuses.name != 'Closed';

The closest functionality I have found in Eloquent is:

$tickets = Ticket::with(array('status' => function($q) {
    return $q->where('name', '!=', 'Closed');
}))->get();

This will still return all of the tickets but only the statuses relation if the name is not closed.

Also, I know this can be done in Fluent, but I would like to work with the returned structure of nested objects that Eloquent provides:

echo ticket->status->name;

Fluent returns a flattened result as a joined query would.

Answer 1

It seems this question is old, but in case if you are looking an real answer here it is, we should try our best to avoid querying the database too much, the accepted answer do it twice but you can get this done by one shot like this

$tickets = Ticket::with('status')->whereHas('status', function($q) {
    return $q->where('name', '!=', 'Closed');
})->get();

Answer 2

I feel like I was trying to make it too complicated.

$statuses = Status::where('name', '!=', 'Closed')->list('id');
$tickets = Ticket::with('status')->whereIn('status_id', $statuses)->get();

Or, I could go the direction of Statuses... which I don't really want to do but it would reduce my query count:

$statusTickets = Status::with('ticket')->where('name', '!=', 'Closed')->get();