CODEWITHSUNDEEP
phpmysqllaraveleloquent
P. Nick
P. Nick

Reputation: 991

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

I have two tables, items and item_values.

items    item_values
-----    -----------
id       item_id
name     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?

Upvotes: 0

Views: 403

Answers (1)

Hamid Ali
Hamid Ali

Reputation: 885

using INNER JOIN will only return those rows from item table which match exactly with item_values table

suppose value_id array contains [1, 2, 3, 4, 5] then raw SQL query would be like this.

SELECT items.* 
FROM items
INNER JOIN item_values on items.id = item_values.item_id
WHERE item_values.value_id IN (1, 2, 3, 4, 5)

WHERE clause returns those item rows having value_id in values array

lets convert it into the Query Builder syntax now

if($request->has("values")) {
    $values = $request->input("values");

    $items = Items::join('item_values', 'item_values.item_id', '=', 'items.id')
             ->whereIn('item_values.value_id', $values)->get();
}

Upvotes: 2

Related Questions