How to organize a filter following the example of Yandex market, sockets?

Question

MySQL

Good afternoon!
A site is planned where users themselves can add characteristics for each product. Further, the characteristics go to the category filter, so that users can search for them.
At the moment, the database looks like this.
Now the problem arises that you need to hide extra parameters in the filter and count the number of products with this parameter.
Examples:
https://market.yandex.ru/catalog/54913/list?hid=90... - dl3.joxi.net/drive/0011/2177/780417/151223/c8fc7d2...
rozetka.com.ua /mp3-pmp/fiio/c80016/v2533 - dl3.joxi.net/drive/0011/2177/780417/151223/3b0c487...
How can such a filter structure be organized?

Answer 1

by count - well, you get an array of selected filters, and apply them sequentially with a sql query.
for example, if you were asked to filter, so that fields with ID=500 had the value 100500 and product_fields was 64, and metro ID=128,
then something like this sql would be collected:

select
    count(DISTINCT p.ID)
from
    products p
        join folder_fields ff on p.folder_id = ff.folder_id
        join fileds f on ff.field_id = f.id
        join product_fields pf on pf.product_id = p.id
where 1=1
    and f.id=500
    and f.name=100500
    and pf.value=64
    and p.metro_id=128

and to hide the excess - the filter should respond to the choice already made ...
for example, the user wants computers - you display filters by processors / video cards, etc., he wants smartphones - the manufacturer and the operating system, and so on.
you can use ajax, you can immediately hardcode this nesting and load it to the user with json...