CODEWITHSUNDEEP
phplaraveleloquent
Devon Bessemer
Devon Bessemer

Reputation: 35337

orWhere in a relationship method

Does anyone know of a way to include an orWhere in a relationship to allow more than one value to match the key?

An example is we have a Product model and the Product can be a child of another Product (parent_id) and we want to pull the items from both the product and the parent.

For the relationship we may want:

public function items()
{
   return $this->hasMany(Item::class)
               ->orWhere('product_id', $this->parent_id);
}

The above example works on matching the product_id against the id and parent_id but devolves quickly when we try to do more with that relationship because it does not nest the where call.

So the SQL would be: SELECT * FROM items WHERE product_id = x OR product_id = y

So when we do something like $product->items()->where('value', '>', 1); it will break the relationship because it does:

SELECT * FROM items WHERE product_id = x OR product_id = y AND value > 1

instead of:

SELECT * FROM items WHERE (product_id = x OR product_id = y) AND value > 1

We have a workaround without utilizing an Eloquent relationship (merging two collections), but I would like to take advantage of a relationship method if possible.

Upvotes: 1

Views: 231

Answers (1)

Razor
Razor

Reputation: 9835

As @Danny commented, it's a misuse of the hasMany relationship, merging $product->items and $product->parent->items relations would be more appropriate. However, if you want a workaround solution, you may create the relationship manually :

public function items() // I would rather create another method for this specific relationship
{
    $foreignKey = $this->getForeignKey();

    $instance = new Item;

    $localKey = $this->getKeyName();

    $hasMany = new \Illuminate\Database\Eloquent\Relations\HasMany(
        $instance->newQuery()->whereRaw('(true'),
        $this,
        $instance->getTable().'.'.$foreignKey,
        $localKey
    );

    return $hasMany->orwhereRaw('product_id = ? )', [$this->parent_id]);
}

So your query would be 

SELECT * FROM items WHERE (true and product_id = x OR product_id = y) AND value > 1

I can't figure out how to remove the useless true condition, Laravel keeps adding and keyword behind the scene.

Upvotes: 1

Related Questions