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();

Answer 3

You can override newQuery in your model so that an additional query is applied to all get() methods - see http://usman.it/filter-eloquent-results-overriding-laravel/

Answer 4

You might be able to define a hasOne('status') relationship in your model and use that but I haven't personally tried to do that.

Answer 5

Try adding query scopes to your model relations. Laravel. Use scope() in models with relation

Laravel 4.2+

Answer 6

Another clean approach would be to create a separate relation for the closed tickets

Relationship inside Ticket Model

//get tickets that are not Closed
public function openTickets()
{
    return $this->hasMany('App\Models\Status')->where('name','!=','Closed');
}

Usage

$tickets = Ticket::with('openTickets')->get();