How are complex filters arranged in online stores?

Question

Web development

Hello, please tell me how complex product filters are arranged in online stores, for example here https://www.dns-shop.ru/catalog/17a899cd16404e77/p...
The bottom line is that when you select another filter, many other filters are blocked if There are no products for these filters, and it turns out that there are no empty pages without products. The very logic of constructing tables or similar queries is interesting, can any additional table links be created? It’s just that in my view it’s a lot of database queries to calculate which filters have products and which ones don’t, and even show the number of products next to each filter. Prompt who faced where it is possible to find the information on this subject.

Answer 1

The secret is that these requests are not made to the database. This is a faceted search performed by a full text search engine like Sphinx or Elasticsearch.

Answer 2

In short: this is a cascade inverse filter (reverse "waterfall"), which is built from the "leaf" (the "deepest" nesting) in the opposite direction (up the "tree").
That is, as follows:
1. We take the number of products for all "sheet" properties / parameters (select count) in the desired category (it is better to prepare and update these tables in advance, when adding / removing products),
2. Select the property with the smallest number goods
3. Add a condition with this property to the query selection filter at each iteration of the expression construction and look for the number of goods at the level above...
4. If all zeros are at the current level, exit.
5. GOTO 2

Answer 3

another filter, many other filters are blocked if there are no products matching these filters

Something like this, I don't know if it's correct:
And there, each property can also have an attribute that determines which filter interface will be used.