Laravel Eloquent Faceted Search, is this query possible?

Question

I have a table inventory, which has a one-to-many relationship to a table called data. I'm creating a filter application, where the user will be able to filter inventory by color and by material for example. It's important that this information is stored on the data table for compatibility purposes.

The data table was created to be flexible in terms of scaling the data for inventory. Data table has these columns:

inventory_id
key
value

What I want to accomplish is if a user is looking for inventory that has material of cotton and a color blue. I want the results to ONLY show inventory that is both.

The issue with my query is that it's showing inventory that has a material of cotton or is the color blue. My query looks like this so far:

$values = array_merge($materialValues, $colorValues);

Inventory::query()->whereHas('data', function ($q) {
    return $q->whereIn('key', [
        Enum::MATERIAL->key,
        Enum::COLOR->key
    ])->whereIn('values', $values);
})

I've tried something like this for example, which just breaks the query.

Inventory::query()->whereHas('data', function ($q) {
    return $q->where('key', Enum::MATERIAL->key)
        ->whereIn('value', $materialValues)
        ->where('key', Enum::COLOR->key)
        ->whereIn('value', $colorValues);
})

Also, note that I need to keep this as a builder query since I need to use the paginate method on it.

I feel stuck, is this even possible with eloquent?

Answer 1

try something like this:

Inventory::query()
  ->whereHas('data', function ($q) use ($colorValues) {
      return $q->where('key', Enum::COLOR->key)
          ->whereIn('value', $colorValues);
  })
  ->whereHas('data', function ($q) use ($materialValues) {
      return $q->where('key', Enum::MATERIAL->key)
          ->whereIn('value', $materialValues);
  });

Answer 2

You should do eloquent query to use subqueries for each condition, so that each condition is applied separately and that only items matching all conditions are returned:

Inventory::query()
    ->whereHas('data', function ($q) use ($materialValues) {
        $q->where('key', Enum::MATERIAL->key)
          ->whereIn('value', $materialValues);
    })
    ->whereHas('data', function ($q) use ($colorValues) {
        $q->where('key', Enum::COLOR->key)
          ->whereIn('value', $colorValues);
    })
    ->paginate(10);