Tricky query solution

Question

Hello everyone,

Does anyone have any idea on how can you create a product filtering query (or queries) that will emulate the results on this page?

http://www.emag.ro/notebook%5Flaptop

Explanation

If you press HP as a brand, the page will show you all the HP products, and the rest of the available filters are gathered from this query result. Fine and dandy until now, I got this licked w/o any problems.

Press 4GB Ram, and ofcourse you will see all HP products that have this property/feature. Again fine and dandy, got no problems until here.

BUT if you look closely you will see that the Brand features now show also, let's say Acer, having a few products with the 4GB feature, and maybe more after Acer, and the checkbox isn't yet pressed.

The only ideea that comes to mind is to make that much more queries to the database to get these other possibilities results.

After you start checking the 3rd possible option (let's say Display size) the things start to complicate even more.

I guess my question is: Does anyone has any idea on how to make this w/o taxing the server with tons of queries ?

Thank you very much for reading this far, I hope I made myself clear in all this little story.

Answer 1

Take a look at sql

UNION

syntax.

"UNION is used to combine the result from multiple SELECT statements into a single result set."

Answer 2

It could be that there is some sophisticated means of determining some computed distance of a result from your criteria, but maybe it is as simple as using an OR in the query rather than an AND.

Answer 3

It's not really "tons" of queries, it's one query per attribute type (brand, RAM, HDD). Let's say you have selected HP, 4GB RAM and 250GB disk. Now for each attribute type select products according to the filter, except for the current type, and group by results by the current type. In a simplistic model, the queries could look like this:

SELECT brand, count(*) FROM products WHERE ram='4BG' AND disk='250GB' GROUP BY brand
SELECT ram, count(*) FROM products WHERE brand='HP' AND disk='250GB' GROUP BY ram
SELECT disk, count(*) FROM products WHERE brand='HP' AND ram='4BG' GROUP BY disk
SELECT cpu, count(*) FROM products WHERE brand='HP' AND ram='4BG' AND disk='250BG' GROUP BY cpu
...

You should have indexes on the columns, that every query doesn't do a sequential scan over the table. Of course there are some "popular" combinations and you will likely have to display the same numbers on multiple pages when the user is sorting/navigating the list, so you might want to cache the numbers and invalidate the cache on update/insert/delete.