30

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?

Rob
  • 10,851
  • 21
  • 69
  • 109
  • 4
    The answer below from Wallace Maxters (http://stackoverflow.com/a/27742997/2111952) would now seem to be the correct answer. Laravel now has whereDoesntHave - not sure if it did when the question was original asked. – casafred May 20 '15 at 11:12

8 Answers8

54

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.

Wallace Vizerra
  • 3,382
  • 2
  • 28
  • 29
  • 1
    Here's the link to whereDoesntHave in the API docs: http://laravel.com/api/5.0/Illuminate/Database/Eloquent/Builder.html#method_whereDoesntHave Wallace seems to have found the best answer (perhaps it wasn't available before) – casafred May 20 '15 at 11:08
  • Exactly! just I found stirring in the source code (as I always do). I did not know at the time Laravel 5 – Wallace Vizerra Jul 08 '15 at 15:33
13

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

// User model
public 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();
Makita
  • 1,812
  • 12
  • 15
  • Actually ended up going with this solution. The answer I posted myself doesn't work if the pivot table is empty. – Rob Nov 22 '13 at 07:35
  • 1
    Accordding to Laravel documentation, `availableItems` should be [`scopeAvailableItems`](http://laravel.com/docs/4.2/eloquent#query-scopes) – Wallace Vizerra Jul 15 '15 at 11:47
  • Indeed, question should be tagged with a version number. I believe it was v4.0 or v4.1 – Makita Jul 17 '15 at 02:49
  • 2
    In Laravel 5.5+ you may swap the `->lists('user_id')` method with `->pluck('user_id)` – Gkiokan Oct 31 '17 at 22:40
6

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();
Collin James
  • 9,062
  • 2
  • 28
  • 36
  • This seems interesting, however I guess it'r running the extra query to get the list of items? – Rob Nov 21 '13 at 11:35
  • It compiles into a subquery so it's only one request to the DB. http://dev.mysql.com/doc/refman/5.0/en/subqueries.html – Collin James Nov 21 '13 at 14:45
  • 4
    I am using **laravel 5** and had to replace `->table` with `->from`. Otherwise I get a *BadMethodCallException Call to undefined method Illuminate\Database\Query\Builder::table()* – Alexander Taubenkorb Apr 04 '15 at 20:38
2

How about left join?

Assuming the tables are users, items and item_user find all items not associated with the user 123:

DB::table('items')->leftJoin(
    'item_user', function ($join) {
        $join->on('items.id', '=', 'item_user.item_id')
             ->where('item_user.user_id', '=', 123);
    })
    ->whereNull('item_user.item_id')
    ->get();
mp31415
  • 6,531
  • 1
  • 44
  • 34
0

this should work for you

$someuser = Auth::user();     

$someusers_items = $someuser->related()->lists('item_id');

$all_items = Item::all()->lists('id');

$someuser_doesnt_have_items = array_diff($all_items, $someusers_items); 
Gadoma
  • 6,475
  • 1
  • 31
  • 34
0

Ended up writing a scope for this like so:

public function scopeAvail($query)
{
    return $query->join('item_user', 'items.id', '<>', 'item_user.item_id')->where('item_user.user_id', Auth::user()->id);
}

And then call:

Items::avail()->get();

Works for now, but a bit messy. Would like to see something with a keyword like not:

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

Basically Eloquent is running the above query anyway, except with a = instead of a <>.

Rob
  • 10,851
  • 21
  • 69
  • 109
0

Maybe you can use:

DB::table('users')
        ->whereExists(function($query)
        {
            $query->select(DB::raw(1))
                  ->from('orders')
                  ->whereRaw('orders.user_id = users.id');
        })
        ->get();

Source: http://laravel.com/docs/4.2/queries#advanced-wheres

Turcko007
  • 21
  • 3
-1

This code brings the items that have no relationship with the user.

$items = $this->item->whereDoesntHave('users')->get();

  • 1
    Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post – chrslg Jan 13 '23 at 20:43