How to use mySQL to filter products by EAV properties if more than one property is specified?

Question

MySQL

I'm doing a product filter by properties, it comes to the fact that I'm writing an additional condition (the attribute code is X and the attribute value = Y AND the code of another attribute from the same table is Z and the attribute value is I)

(
AND ((`lists_items`.`slug`='biglist') AND (`parameters`.`slug`='list-size'))
AND - !его заменял на OR
((`lists_items`.`slug` IN ('black', 'green')) AND (`parameters`.`slug`='tea-type'))
)

where lists_items are the list elements from which the property values ​​are selected
parameters is the entity for the product parameter
products_parameters is the entity for the product parameter values, here the possible attribute value from lists_items and the product from products
products are associated - a table with products
*If I replace the marked AND with OR - finds either goods by one property, or by another. But with AND it doesn't find ANYTHING. Although there are products that match both criteria - that is, there are products that have both properties and one of the passed values.
The entire query is below (biglist is the value of the list-size property, and black and green are the values ​​of the tea-type parameter).

SELECT * FROM `products` LEFT JOIN `sub_sections` ON `products`.`subsection_id` = `sub_sections`.`id`
 LEFT JOIN `products_parameters` ON `products`.`id` = `products_parameters`.`product_id`
 LEFT JOIN `lists_items` ON `products_parameters`.`list_item_id` = `lists_items`.`id` 
LEFT JOIN `parameters` ON `products_parameters`.`parameter_id` = `parameters`.`id`
 WHERE (`sub_sections`.`slug`='first_sub')
 AND (((`lists_items`.`slug`='biglist') AND (`parameters`.`slug`='list-size')) 
AND ((`lists_items`.`slug` IN ('black', 'green')) AND (`parameters`.`slug`='tea-type')))

I write in Yii2, so an explanation right away in ActiveQuery will also help a lot.
If AND, which I indicated to replace with OR - finds true - that is, either the property has such and such a value, or another property matches with a different value. But I need matches for all properties, so I write AND. And here I always have an empty result. Why could this be? What is the final request? Killed all day for this.

Answer 1

TOTAL
Decided with this query

SELECT p.*, po1.slug 'P1', po2.slug 'P2', prop1.slug 'S1', prop2.slug 'S2' 
 
FROM products p       
 
INNER JOIN products_parameters p2o1 ON p.id = p2o1.product_id
INNER JOIN lists_items po1  ON po1.id = p2o1.list_item_id
LEFT JOIN parameters prop1 ON prop1.id = p2o1.parameter_id
 
INNER JOIN products_parameters p2o2 ON p.id = p2o2.product_id
INNER JOIN lists_items po2  ON po2.id = p2o2.list_item_id
LEFT JOIN parameters prop2 ON prop2.id = p2o2.parameter_id
WHERE
(`po1`.`slug` IN ('black', 'green') AND `prop1`.`slug` = 'tea-type') AND (`po2`.`slug`='biglist' AND `prop2`.`slug` = 'list-size')

I do not know how clumsy this method is. Now I will work on porting this code to Yii2, apparently I will have to abandon ActiveQuery and form it through Query.

Answer 2

1. You explained hilariously.
2. For filters, it's easier to make a SearchModel, especially since it can be generated via GII, resulting in a ready-made solution, to which it remains to add join
3. Here's what you should read:
www.yiiframework.com/wiki/653/displaying-sorting-a. ..
https://nix-tips.ru/yii2-sortirovka-i-filtr-gridvi...
www.yiiframework.com/doc-2.0/guide-db-active-recor...
After reading, you should have specific questions, I'll be happy to answer them.