How to create a query in the database of three tables, with a specific filter?

Question

Laravel

Laravel 8.

I recently started learning the Laravel framework and decided to start by creating an online store.
The task was set as follows: to implement the dynamic addition of attributes for certain subcategories.

I built the following logic:
I have 3 tables interconnected, in the picture these tables


are Here is my query:

SELECT * FROM `product`
JOIN `product_attributes`
ON `product_attributes`.`product_id` = `product`.`id`
JOIN `attribute`
On `attribute`.`id` = `product_attributes`.`attribute_id`
WHERE (`attribute`.`name_attribute` = 'size' AND `product_attributes`.`value` = '256')

With a filter with one attribute, everything works fine, but if I add a second attribute, it displays 0 rows.

The second attribute is added like this:

SELECT * FROM `product`
JOIN `product_attributes`
ON `product_attributes`.`product_id` = `product`.`id`
JOIN `attribute`
On `attribute`.`id` = `product_attributes`.`attribute_id`
WHERE (`attribute`.`name_attribute` = 'size' AND `product_attributes`.`value` = '256')
AND (`attribute`.`name_attribute` = 'color' AND `product_attributes`.`value` = 'red')

Maybe I'm not adding attributes correctly, why are there no results when filtering by two attributes?

Answer 1

In my humble opinion, if Laravrel, then eloquent and not joins.
Your task is solved through many to many relationships and eloquent queries.
You link models through belongsToMany.
Create a table attribute_product (in alphabetical order and units. Number)
In the docks, everything is clearly written.
And then you attach withPivot and additional to the models. columns.
In any case, by keywords laravel manyToMany, withPivot, whereHas you will find a lot of information