Select all records where a relationship count is zero using Eloquent?

Question

In plain English: I have three tables. subscription_type which has many email_subscriptions which has many emails.

I'm trying to select all email_subscription records that have a particular subscription_type, that also don't have any associated email records that have a status of Held.

The particular bit I am stuck on is only returning email_subscriptions which have zero emails (with an additional where clause stacked in there described above).

Using Eloquent, I've been able to get a bit of the way, but I don't have any idea how to select all the records that have a relationship count of zero:

$subscriptionsWithNoCorrespondingHeldEmail = EmailSubscriptions::whereHas('subscriptionType', function($q) {
            $q->where('name', 'New Mission');
        })-; // What do I chain here to complete my query?

Additionally, is this even possible with Eloquent or will I need to use Fluent syntax instead?

Answer 1

You can use the has() method to query the relationship existence:

has('emails', '=', 0)

Eg:

$tooLong = EmailSubscriptions::whereHas('subscriptionType', function($q) {
    $q->where('name', 'New Mission');
})->has('emails', '=', 0)->get();

Edit

You can do more advanced queries with the whereHas() and whereDoesntHave() methods:

$tooLong = EmailSubscriptions::whereHas('subscriptionType', function($q) {
    $q->where('name', 'New Mission');
})
->whereDoesntHave('emails', function ($query) {
    $query->where('status', '=', 'whatever');
})->get();