Where NOT in pivot table

laravel laravel-4 pivot-table eloquent
Rob picture Rob · Nov 20, 2013 · Viewed 19.8k times · Source

In Laravel we can setup relationships like so:

class User {
    public function items()
    {
        return $this->belongsToMany('Item');
    }
}

Allowing us to to get all items in a pivot table for a user:

Auth::user()->items();

However what if I want to get the opposite of that. And get all items the user DOES NOT have yet. So NOT in the pivot table.

Is there a simple way to do this?

Answer

Wallace Maxters picture Wallace Maxters · Jan 2, 2015

Looking at the source code of the class Illuminate\Database\Eloquent\Builder, we have two methods in Laravel that does this: whereDoesntHave (opposite of whereHas) and doesntHave (opposite of has)

// SELECT * FROM users WHERE ((SELECT count(*) FROM roles WHERE user.role_id = roles.id AND id = 1) < 1)  AND ...

    User::whereDoesntHave('Role', function ($query) use($id) {
          $query->whereId($id);
    })
    ->get();

this works correctly for me!

For simple "Where not exists relationship", use this:

User::doesntHave('Role')->get();

Sorry, do not understand English. I used the google translator.

Related questions

How to filter a pivot table using Eloquent?

I'm using a pivot table on the project I'm working with to get works of users. E.g: User::find(1)->works gives me the works of user with ID of 1. The thing is that I want to filter …

Read More
Laravel attach pivot to table with multiple values

Background I'm creating a database revolving around food allergies and I have a many to many relationship between foods and allergies. There is also a pivot value called severity which has a numerical number representing the severity of the allergy …

Read More
How is a pivot table created by Laravel?

In Laravel 4, when working with many-to-many relationships as described in the 4.2 docs, how can I actually get Laravel to create the pivot table for me? Do I need to add something in my migrations for the two models that are …

Read More