Laravel Scope 按数据透视值 [英] Laravel Scope by pivot data values

问题描述

假设我想存储有关客户的各种数据，所以我有两个模型通过数据透视表链接，在数据透视表上存储每种数据字段类型的客户值:

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');
    }
}

和

Datafield {
    public function customers()
    {
        return $this->belongsToMany('Customer')->withPivot('value');
    }

所以我的表是客户、客户数据字段、数据字段.

So my tables are customers, customer_datafield, datafields.

如何在客户中设置查询范围以查找特定数据字段的值为 x 的所有客户?

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?

类似的东西

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!

推荐答案

单个固定枢轴字段的雄辩方式:

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();

您可能想在第二个闭包中使用 where 和值数组而不是 foreach，但它可能无法按预期工作，因为字段不会以表名为前缀.

You might be tempted to use where with array of values instead of foreach in the second closure, however it might not work as expected, for fields won't be prefixed with table name.

另一种解决方案是使用简单的join:

Another solution is to use 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.

这篇关于Laravel Scope 按数据透视值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！