jeudi 12 mars 2020

How to get an exact match with multiple where clauses in relation from other table?

I have two tables, items and item_values.

items
-----
id
name


item_values
-----------
item_id
value_id

I've set up a relationship between these two tables in my item model.

function values() {
    return $this->hasMany(ItemValues::class, "item_id", "id");
}

I have an array containing value_id for example like [1, 2, 3, ...] and I want to get all items from items table if they exist in item_values and if the value_id is in the array above. I want it to be an exact match, so I don't want it to display all items with either one of the values, but I want them to display all items that have all of the values.

$item = Items::orderBy("created_at", "desc");
if($request->has("values")) {
    $appends["values"] = $request->input("values");
    $values = $request->input("values");

    $findValues = $item->whereHas("values", function($query) use($values) {
        foreach($values as $value) {
            $query->where("value_id", $value);
        }
    });
}

$items = $item->paginate(10);

This works for a single value, however, if I attempt to search for multiple values it will return 0 results.

When I print out the query it returns:

select * from `items` where exists (select * from `item_values` where `items`.`id` = `item_values`.`item_id` and `value_id` = ? and `value_id` = ?) order by `created_at` desc

How can I achieve what I want?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire