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

所以我的表是customers，customer_datafield，datafields.

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屋！