What is the principle of creating a filter in an online store that does not allow an empty result?

Question

SQL

Good day.
Available:

• Online store with a database of goods
  
• Goods have a certain set of characteristics
  

Task:

• Develop a filter panel by product characteristics that would not allow the user to set a combination that led to an empty result.
  

Until we make a query to the database, we cannot predict the result. The exception is if SELECT ... FROM ... WHERE prm1=... returns an empty result, then SELECT ... FROM ... WHERE prm1=... AND prm2=... AND prm3=... will also return an empty result.
How to proceed?
In the WHERE section of the query, add one condition at a time until the result is empty?
Make a batch of queries to the database with all possible combinations WHERE? - SQL-server "will lay down".
Go from the opposite? Make one query with all parameters, and then sequentially remove one by one from WHERE and analyze the result? But this way, too, there will be a whole pile of useless SELECTs ...
Pre-analyze all parameter values, fix min / max and form a filter panel based on them? But with this approach, the problem is still not completely solved.

Answer 1

This task does not fit well with a relational DBMS , which Sergey Gornostaev already wrote to you about. What you
are doing is faceted search. It is perfectly implemented by DBMS, which are able to so-called. full text search.
The fact is that to implement a full-text search, bit indexes are used, which, in addition to their main search task, allow you to immediately determine the number of "documents" included in the selection (according to the terminology: what is called "records" in relational DBMS is called "documents" in full-text search engines - such a tradition).
You, on the other hand, are using a relational DBMS that is not suitable for this task. And in relational DBMS, determining the number of records that got into the answer,.
Using a full-text search engine, you could apply filters simply to find out how many entries a response would result in, and then turn them off if there are too many entries. In full-text search engines, this is a cheap (fast) operation.
But in relational DBMS, and you use just such, this is a very expensive operation. And you can't do that. Unless, of course, you want your site to slow down.
One of the fastest full text search engines is SphinxSearch and its fork of Manticore.
PySy:
Why full-text search engines are ideal for faceted search implementation.
1) Search by name should still be on the site. A full-text search engine, unlike a conventional relational DBMS engine, can search by any part of the name, by product description
... ):
a) The phrase is divided into separate words, while auxiliary words (prepositions, conjunctions, articles, etc.) are discarded
b) Words are run through the stemming algorithm to cut off the endings snowball.tartarus.org/algorithms/russian/stemmer. html
c) The resulting words without endings (terms) are placed in the simplest storage of the "key-value" type, where the key is the term. The value is a large bit vector of type 00010101011110010000000011111....., where each zero and one position corresponds to a document (in your case, a product). For compactness, the representation RoaringBitmap roaringbitmap.org
is used d) With the resulting bit vectors, you can extremely quickly perform any logical operations AND, OR, NOT
How is a faceted search based on a full-text index?
It's very simple, we introduce an artificial term "color = green", we build a bit vector on it; we introduce an artificial term "color = red", we build a bit vector on it; we introduce an artificial term "view=boot" and build a bit vector based on it; we introduce the term "view=boot" and build a bit vector based on it; we introduce the terms "size=40", "size=41", "size=42", "size=43" and build a bit vector for each of them.
After that, finding "size 42 green shoes" is just an AND operation on 3 bit vectors.
And once again - this is all done by the engine, you do not need to do this manually.

Answer 2

Offload the task of faceted search to some full-text search engine that is more designed for this than RDBMS.

Answer 3

They would take any engine and see why the BIKE should be reinvented?
There are intelligent filters in opencart, minishop2, and Bitrix.
Everywhere they are naturally paid ....
Although I’m probably in vain about Bitrix, this is not yours))))
Minishop - install the miniShop2 + mSearch2 package for free on demo servers, study it .
On the demo, just as you need - _minishop2.com/catalog/
Bitrix - install the demo version for free, look for a filter with a free demo installation - that's it! further a week or how much the developer of the filter will give there - rummage .
But opencart would seem to be free in essence, but you can’t just install the filter just like that, only prepayment))

Answer 4

Display, in addition to creations for this query, also suitable parameters for the filters themselves!
Here is how the variants of one “Collection” parameter look like without sorting by the “Brand” parameter
And here is how with the “Brand” sorting
There will always be fields in the filters that match others and there will always be at least 1 product