php Laravel，如何为关系列使用 where 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14621943/
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
Laravel, How to use where conditions for relation's column?
提问by Tharshan Venkdesan
I'm using Laravel and having a small problem with Eloquent ORM.. I can get this working simply with SQL query using a JOIN but I can't seem to get it working with Eloquent!
我正在使用 Laravel 并且 Eloquent ORM 有一个小问题。我可以使用 JOIN 简单地使用 SQL 查询来实现它，但我似乎无法让它与 Eloquent 一起工作！
This is what I want, I have two tabels. one is 'Restaurants' and other is 'Restaurant_Facilities'.
这就是我想要的，我有两个表。一个是“Restaurants”，另一个是“Restaurant_Facilities”。
The tables are simple.. and One-To-One relations. like there is a restauranttable with id, name, slug, etc and another table called restaurant_facilitieswith id, restaurant_id, wifi, parking, etc
表格很简单......和一对一的关系。像有一个restaurant带桌子id，name，slug，等和另一个表称为restaurant_facilities有id，restaurant_id，wifi，parking，等
Now what I want to do is.. load all restaurants which have wifi = 1 or wifi = 0.. How can i do that with Eloquent ? I have tried eager loading, pivot tables, with(), collections() and nothing seems to work!
现在我想要做的是.. 加载所有有 wifi = 1 或 wifi = 0 的餐厅.. 我怎么能用 Eloquent 做到这一点？我尝试过急切加载、数据透视表、with()、collections()，但似乎没有任何效果！
The same problem I have for a Many-To-Many relation for cuisines!
I have the same restauranttable and a cuisinetable and a restaurant_cuisine_connectiontable..
对于cuisines!的多对多关系，我遇到了同样的问题！我有同restaurant一张桌子和一张cuisine桌子和一张restaurant_cuisine_connection桌子..
but how do I load all restaurants inside a specific cuisine using it's ID ?
但是如何使用它的 ID 将所有餐厅加载到特定美食中？
This works.
这有效。
Cuisine::find(6)->restaurants()->get();
Cuisine::find(6)->restaurants()->get();
but I wanna load this from Restaurant:: model not from cuisines.. because I have many conditions chained together.. its for a search and filtering / browse page.
但我想从 Restaurant:: 模型而不是从菜系加载它.. 因为我有很多条件链接在一起.. 它用于搜索和过滤/浏览页面。
Any ideas or ways ? I've been struggling with this for 3 days and still no answer.
任何想法或方法？我已经为此苦苦挣扎了 3 天，但仍然没有答案。
Example Models :
示例模型：
class Restaurant extends Eloquent {
protected $table = 'restaurants';
public function facilities() {
return $this->hasOne('Facilities');
}
}
class Facilities extends Eloquent {
protected $table = 'restaurants_facilities';
public function restaurant() {
return $this->belongsTo('Restaurant');
}
}
PS : This seems to be working.. but this is not Eloquent way right ?
PS：这似乎有效..但这不是雄辩的方式吗？
Restaurant::leftJoin(
'cuisine_restaurant',
'cuisine_restaurant.restaurant_id',
'=', 'restaurants.id'
)
->where('cuisine_id', 16)
->get();
Also what is the best method to find a count of restaurants which have specific column value without another query ? like.. i have to find the total of restaurants which have parking = 1 and wifi = 1 ?
另外，在没有其他查询的情况下查找具有特定列值的餐馆数量的最佳方法是什么？就像..我必须找到有停车位 = 1 和 wifi = 1 的餐厅总数？
Please help on this.
请帮忙解决这个问题。
Thank you.
谢谢你。
采纳答案by JeffreyWay
I don't see anything wrong with doing the left join here, if you have to load from the Restaurant model. I might abstract it away to a method on my Restaurant model, like so:
如果您必须从 Restaurant 模型加载，我认为在此处执行左连接没有任何问题。我可能会将它抽象为我的 Restaurant 模型上的一个方法，如下所示：
class Restaurant extends Eloquent {
protected $table = 'restaurants'; // will be default in latest L4 beta
public function facility()
{
return $this->hasOne('Facility');
}
// Or, better, make public, and inject instance to controller.
public static function withWifi()
{
return static::leftJoin(
'restaurant_facilities',
'restaurants.id', '=', 'restaurant_facilities.restaurant_id'
)->where('wifi', '=', 1);
}
}
And then, from your routes:
然后，从您的路线：
Route::get('/', function()
{
return Restaurant::withWifi()->get();
});
On the go - haven't tested that code, but I think it should work. You could instead use eager loading with a constraint, but that will only specify whether the facility object is null or not. It would still return all restaurants, unless you specify a where clause.
在旅途中 - 尚未测试该代码，但我认为它应该可以工作。您可以改为使用带有约束的预加载，但这只会指定工具对象是否为空。除非您指定 where 子句，否则它仍会返回所有餐厅。
(P.S. I'd stick with the singular form of Facility. Notice how hasOne('Facilities')doesn't read correctly?)
（PS 我会坚持使用 Facility 的单数形式。注意怎么hasOne('Facilities')读不正确？）
回答by Throttlehead
I stumbled across this post while trying to improve my REST API methodology when building a new sharing paradigm. You want to use Eager Loading Constraints. Let's say you have an api route where your loading a shared item and it's collection of subitems such as this:
我在构建新的共享范式时尝试改进我的 REST API 方法时偶然发现了这篇文章。您想使用Eager Loading Constraints。假设您有一个 api 路由，您可以在其中加载共享项及其子项的集合，例如：
/api/shared/{share_id}/subitem/{subitem_id}
When hitting this route with a GET request, you want to load that specific subitem. Granted you could just load that model by that id, but what if we need to validate if the user has access to that shared item in the first place? One answer recommended loading the inversed relationship, but this could lead to a confusing and muddled controller very quickly. Using constraints on the eager load is a more 'eloquent' approach. So we'd load it like this:
当使用 GET 请求访问此路由时，您希望加载该特定子项。当然，您可以通过该 id 加载该模型，但是如果我们首先需要验证用户是否有权访问该共享项呢？一个答案建议加载反向关系，但这可能会很快导致令人困惑和混乱的控制器。对急切负载使用约束是一种更“雄辩”的方法。所以我们会像这样加载它：
$shared = Shared::where('id', $share_id)
->with([ 'subitems' => function($query) use ($subitem_id) {
$query->where('subitem_id', $subitem_id)
}]);
So where only want the subitem that has that id. Now we can check if it was found or not by doing something like this:
所以只想要具有该 id 的子项。现在我们可以通过执行以下操作来检查它是否被找到：
if ($shared->subitems->isEmpty())
Since subitems is a collection (array of subitems) we return the subitem[0] with this:
由于子项是一个集合（子项数组），因此我们返回子项[0]：
return $shared->subitems[0];
回答by Eric Tucker
Use whereHasto filter by any relationship. It won't join the relation but it will filter the current model by a related property. Also look into local scopes to help with situations like this https://laravel.com/docs/5.3/eloquent#local-scopes
用于whereHas按任何关系过滤。它不会加入关系，但会通过相关属性过滤当前模型。还要查看本地范围以帮助解决此类情况https://laravel.com/docs/5.3/eloquent#local-scopes
Your example would be:
你的例子是：
Restaurant::whereHas('facilities', function($query) {
return $query->where('wifi', true);
})->get();
Restaurant::whereHas('cuisines', function($query) use ($cuisineId) {
return $query->where('id', $cuisineId);
})->get();
To achieve the same thing with local scopes:
要使用本地范围实现相同的目的：
class Restaurant extends Eloquent
{
// Relations here
public function scopeHasWifi($query)
{
return $query->whereHas('facilities', function($query) {
return $query->where('wifi', true);
});
}
public function scopeHasCuisine($query, $cuisineId)
{
return $query->whereHas('cuisines', function($query) use ($cuisineId) {
return $query->where('id', $cuisineId);
});
}
}
For local scopes you DO NOT want to define them as static methods on your model as this creates a new instance of the query builder and would prevent you from chaining the methods. Using a local scope will injects and returns the current instance of the query builder so you can chain as many scopes as you want like:
对于本地范围，您不想将它们定义为模型上的静态方法，因为这会创建查询构建器的新实例并阻止您链接方法。使用本地范围将注入并返回查询构建器的当前实例，因此您可以链接任意数量的范围，例如：
Restaurant::hasWifi()->hasCuisine(6)->get();
Local Scopes are defined with the prefix scopein the method name and called without scopein the method name as in the example abover.
本地作用域是scope在方法名称中使用前缀定义的，并且在方法名称中没有调用scope，如上例所示。
回答by Yauheni Prakopchyk
Another solution starring whereHas()function:
另一个解决方案主演whereHas()功能：
$with_wifi = function ($query) {
$query->where('wifi', 1);
};
Facilities::whereHas('restaurant', $with_wifi)
Nice and tidy.
漂亮整洁。
回答by Alex Naspo
Do you absolutely have to load it from the Restaurant model? In order to solve the problem, I usually approach it inversely.
你绝对必须从餐厅模型加载它吗？为了解决这个问题，我通常反其道而行之。
Facilities::with('restaurant')->where('wifi' ,'=', 0)->get();
This will get all the restaurant facilities that match your conditions, and eager load the restaurant.
这将获得符合您条件的所有餐厅设施，并急切加载餐厅。
You can chain more conditions and count the total like this..
您可以链接更多条件并像这样计算总数..
Facilities::with('restaurant')
->where('wifi' ,'=', 1)
->where('parking','=', 1)
->count();
This will work with cuisine as well
这也适用于美食
Cuisine::with('restaurant')->where('id','=',1)->get();
This grabs the cuisine object with the id of 1 eager loaded with all the restaurants that have this cuisine
这会抓取 ID 为 1 的菜系对象，其中包含所有拥有该菜系的餐厅
