Laravel Scope 通过数据透视值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26073924/
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 Scope by pivot data values
提问by Rob
Say I want to store various bits of data about customers, so I have two models linked by a pivot table, storing the customer's values for each datafield type on the pivot table:
假设我想存储有关客户的各种数据，因此我有两个由数据透视表链接的模型，在数据透视表上存储每个数据字段类型的客户值：
Customer {
public function datafields()
{
return $this->belongsToMany('Datafield')->withPivot('value');
}
}
and
和
Datafield {
public function customers()
{
return $this->belongsToMany('Customer')->withPivot('value');
}
So my tables are customers, customer_datafield, datafields.
所以我的表是customers、customer_datafield、datafields。
How can I set up a query scope in the customer to find all customers that have a value of x for a specfic datafield?
如何在客户中设置查询范围以查找特定数据字段的值为 x 的所有客户？
Something along the lines of
类似的东西
Customer {
public function datafields()
{
return $this->belongsToMany('Datafield')->withPivot('value');
}
public function scopeSearch($query, $searchfor)
{
return $query->datafields()->pivot()
->where('value', $searchfor)
->where('datafield_id', 123);
}
}
I've tried a few methods but not having any luck geting one to work. Any suggestions much appreciated!
我尝试了几种方法，但没有任何运气让一种方法起作用。任何建议非常感谢！
回答by Jarek Tkaczyk
Eloquent way for a single fixed pivot field:
单个固定枢轴字段的雄辩方式：
public function scopeDataValue($query, $search)
{
$pivot = $this->datafields()->getTable();
$query->whereHas('datafields', function ($q) use ($search, $pivot) {
$q->where("{$pivot}.value", $search);
});
}
// usage
Model::
This gives you more power and flexibility:
这为您提供了更多的功能和灵活性：
public function scopeDataValues($query, array $search, $bool = 'and')
{
$pivot = $this->datafields()->getTable();
$query->whereHas('categories', function ($q) use ($search, $pivot, $bool) {
$q->where(function ($q) use ($search, $pivot, $bool) {
foreach ($search as $field => $value)
{
$q->where("{$pivot}.{$field}", '=', $value, $bool);
}
});
});
}
// usage
Model::dataValues(['value' => 'findMe', 'otherField' => 'findMeToo'])->get();
Model::dataValues(['value' => 'findMe', 'otherField' => 'orFindMe'], 'or')->get();
You might be tempted to use wherewith array of values instead of foreachin the second closure, however it might not work as expected, for fields won't be prefixed with table name.
您可能想在第二个闭包中使用where值数组而不是foreach在第二个闭包中使用，但是它可能无法按预期工作，因为字段不会以表名为前缀。
Another solution is to use simple join:
另一种解决方案是使用 simple join：
public function scopeDataValue($query, $search)
{
$pivot = $this->datafields()->getTable();
// first get array of already joined table
$base = $query->getQuery();
$joins = array_fetch((array) $base->joins, 'table');
$foreignKey = $this->categories()->getForeignKey();
// if table has not been joined, let's do it
if ( ! in_array($pivot, $joins))
{
$query->join($pivot, $this->getQualifiedKeyName(), '=', $foreignKey);
}
$query->where("{$pivot}.correct", $search);
}
// usage
Model::dataValue(2)->take(..)->where(..)->dataValue(5)->get();
You can alter it the same way as 2nd example above.
您可以按照与上面的第二个示例相同的方式对其进行更改。
回答by Felice Ostuni
Note: on Laravel 5 you can simply use this:
注意：在 Laravel 5 上你可以简单地使用这个：
$query->wherePivot('pivotcolumn','=', $search);
