Search code examples
phpmysqllaraveleloquent

Trying to restrict an Eloquent query to a relationship with a count of 0

I have two models (Organizations and Interactions) and I'd like to query the Organization model for all of the Orgs that have no Interactions. Organizations have a one-to-many relationship with Interactions.

I tried looking into anti-joins in raw SQL, but got nowhere. I also wanted to totally avoid anything like getting all of the full Organizations, then iterating through them to check to see if they had any Interactions, because that's completely impractical given the amount of data I'm working with.

To clarify, I want to avoid this:

$organizations = Organization::all();
foreach ($organizations as $org)
    if($org->interactions()->count() == 0){
        //Add the org to an array for later use because it has no interactions
    }

I'm using Laravel 3.x, and I can't upgrade because the project is really big and I don't have the month it would take to upgrade to 4.1 right now. If there's a significantly better way to do stuff like this 4, that would make selling the conversion process easier.

Here's some relevant code:

//From organization.php
public function interactions() {
     return $this->has_many('Interaction');
}


//From interaction.php
public function organization() {
     return $this->belongs_to('Organization');
}
Solution

  • // select all Organization IDs that have at least 1 interaction

    $uniqueOrganizationIDs = DB::raw('SELECT organization_id FROM interactions GROUP BY(organization_id)');

    // Select orgs that were not in the above list.

    Organization::whereNotIn('id', $uniqueOrganizationIDs)->get();