Laravel Scope by pivot data values

Question

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.

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?

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!

Answer 1

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 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.

---

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.

Answer 2

Note: on Laravel 5 you can simply use this:

$query->wherePivot('pivotcolumn','=', $search);