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?

Jocke Med Kniven · Accepted Answer · 2015-07-07T09:52:20.587

14

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

edited Jul 07 '15 at 09:52

answered Jul 07 '15 at 09:39

Jocke Med Kniven

3,909
1
22
23

How do I also add an additional where statement onto this? For example, I only want to check if the count of emails on that relation that *has a specific status* is 0. – marked-down Jul 07 '15 at 09:42
This is my answer, thanks! Looks like I can avoid the relationship count `has` method entirely and just do a whereDoesntHave() call. – marked-down Jul 07 '15 at 09:58

score 0 · Answer 2 · answered Jul 07 '15 at 09:41

OK what I have under stand from your Question is you would like to have a All Emails which have

specific subscription_type, Zero(0) association and status = status

If yes so you canuse array in where statement. Like:

        $q->->where(array('status' => 'status','subscription_type'=>'what ever you want));

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

2 Answers2