laravel 不在数据透视表中的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20089820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Where NOT in pivot table
提问by Rob
In Laravel we can setup relationships like so:
在 Laravel 中，我们可以像这样设置关系：
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?
有没有一种简单的方法可以做到这一点？
采纳答案by Makita
For simplicity and symmetry you could create a new method in the User model:
为了简单和对称，您可以在 User 模型中创建一个新方法：
// 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();
回答by Wallace Maxters
Looking at the source code of the class Illuminate\Database\Eloquent\Builder, we have two methods in Laravelthat does this: whereDoesntHave(opposite of whereHas) and doesntHave(opposite of has)
查看类的源代码Illuminate\Database\Eloquent\Builder，我们在Laravel中有两个方法可以做到这一点：（whereDoesntHave与 的相反 whereHas）和doesntHave（与 的相反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.
对不起，不懂英文。我用了谷歌翻译器。
回答by Collin James
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.
如果这是我经常做的事情，我会将它作为范围方法添加到 Item 模型中。
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();
回答by mp31415
How about left join?
左连接怎么样？
Assuming the tables are users, itemsand item_userfind all itemsnot associated with the user 123:
假设表是users，items并item_user找到所有items与用户无关的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();
回答by Rob
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:
现在可以使用，但有点乱。想看到带有以下关键字的内容not：
Auth::user()->itemsNot();
Basically Eloquent is running the above query anyway, except with a =instead of a <>.
基本上 Eloquent 无论如何都在运行上述查询，除了使用 a=而不是 a <>。
回答by Turcko007
Maybe you can use:
也许你可以使用：
DB::table('users')
->whereExists(function($query)
{
$query->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
回答by Gadoma
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);
