Where NOT in pivot table

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.

For simplicity and symmetry you could create a new method in the User model:

// User modelpublic function availableItems(){    $ids = \DB::table('item_user')->where('user_id', '=', $this->id)->lists('user_id');    return \Item::whereNotIn('id', $ids)->get();}

To use call:

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

It's not that simple but usually the most efficient way is to use a subquery.

$items = Item::whereNotIn('id', function ($query) use ($user_id)    {        $query->select('item_id')            ->table('item_user')            ->where('user_id', '=', $user_id);    })    ->get();

If this was something I did often I would add it as a scope method to the Item model.

class Item extends Eloquent {    public function scopeWhereNotRelatedToUser($query, $user_id)    {        $query->whereNotIn('id', function ($query) use ($user_id)        {            $query->select('item_id')                ->table('item_user')                ->where('user_id', '=', $user_id);        });    }}

Then use that later like this.

$items = Item::whereNotRelatedToUser($user_id)->get();