Referencing foreign key data in WHERE clause in Eloquent

0
votes

I have two tables Songs (belongsTo App\Host) and Hosts (hasMany App\Song). My Songs table has an attempts column and my Host table has a skip_threshold. I would like to query out all Songs that have not met its related Host's skip threshold.

How can I do this?

I have tried something like this:

return $songs = Song::whereIn('host_id', $available_hosts)
                       ->where('attempts', '<', $songs->host->skip_threshold)->get();

I've attempted to make use of Eloquent relationship querying, but from testing I see this won't work. I would like to try and use Eloquent to do this so I can take advantage of eager loading related data in my blade templates.

2
phpmysqllaraveleloquent

2 Answers

1
votes

You could try

$songs = Song::whereHas('host', function($query) {
    $query->where('skip_threshold', '>', DB::raw('songs.attempts'));
})->get();
1
votes

If I'm understanding what you're looking for, you can chain together where clauses, like so:

$songs = Song::whereHas('host', function($query) {
    $query->where('skip_threshold', '>', DB::raw('songs.attempts'));
})->whereIn('host_id', $available_hosts)->get();