Gondwana's avatar

Filtering fields with same properties as Laravel Releationship HasMany

Hello, I am preparing an e-commerce site. I have two tables linked by Releationship. The first table is Products and the second is ProductAttributes. It is linked by product_id in two tables. When I query my Products table, in my Product model file

public function attributes()
    {
        return $this->hasMany(ProductAttribute::class, 'product_id');
    }

I want to extract only eligible products using relation. The query is like this:

                $query->where('slug->tr', $this->slug);
})
->whereHas('attributes' , function($attrquery){
            $attrquery->whereIn('value', $this->select_filter_attr);
})->paginate(100);

But the problem is, when I do whereIN('value' , ['2021','LOW']) using the value field in the ProductAttributes table, it pulls all the products with both 2021 and LOW values, while I want it to pull the products with both 2021 and LOW fields at the same time. How do I do this, thanks in advance.

0 likes
5 replies
manojo123's avatar

You can write the table name as alias of the field

$attrquery->whereIn('mytable.value', $this->select_filter_attr);
1 like
NicolasMica's avatar

Hi 👋

In this case you would have to use the whereHas method for every item in your select_filter_attr array.

Product::where(function ($query) {
    collect($this->select_filter_attr)->map(function ($value) use ($query) {
        $query->whereRelation('attributes', 'value', $value);
    });
})->get();
1 like
Gondwana's avatar

@NicolasMica thank you for your help. I tried this but it fetch all records. Maybe I didn't explain exactly what I wanted to say. e.g. records in my product_attributes table

id: 1, product_id: 53, title:"year" ,value:"2021"

id: 2, product_id: 54, title:"model" ,value:"low"

id: 3, product_id: 53, title:"year" ,value:"low"

If only 2021 in value is queried, product_id: 53 and product_id: 54 are displayed, this is normal. But if I query both ["2021","low"], I only want product_id = 53.

Snapey's avatar

@Gondwana You have to add a whereHas statement for every search term, by using a loop

But whereHas has some performance issues.

You might be better getting a set of results for each term and then finding the common products across the sets

or use a join...

NicolasMica's avatar

@Gondwana It should work as inteded. Notice that I wrapped the whereRelation (shorthand version of whereHas) in a where clause, which means that it will wrap the clause in parentheses, thus avoiding to be affected by orWhere clauses. It will produce the query below:

SELECT * FROM `products` 
WHERE (
		EXISTS (
				SELECT * FROM `product_attributes` 
				WHERE `products`.`id` = `product_attributes`.`product_id`AND `name` = '2021'
		)
		AND EXISTS (
				SELECT * FROM `product_attributes`
				WHERE `products`.`id` = `product_attributes`.`product_id` AND `name` = 'LOW'
		)
)

However as @snapey stated, keep track of the request cost because it can become quite expensive. An alternative solution is to use a subquery to identify which product matches your attributes.

Product::whereIn('id', function ($query) use ($request) {
    $query->select('product_id')
        ->from('product_attributes')
        ->whereIn('name', $this->select_filter_attr)
        ->groupBy('product_id');
})->get();

Please or to participate in this conversation.