Filters [database]

abiko · 03-03-2008, 05:46 PM

I'm wondering how to make dynamic filtering for content.
Example:
I want to have several categories(with subcategories) in which I want to place some articles (some kind of tags).
Filter

Code:

 Color
 ..../White
 ..../Black
 ..../Yellow
 Type
 ..../Box
 ..../Bag
 ..../Unknown

I though I would make one table where filers will be stored and another table in what will be applied filter.
Filter table

Code:

id | filter_name | filter_parent

Filter storage

Code:

filter_id | content_id

I want to be able to filter out content with multiple filter categories.
Example:
I want to find all White Boxes - so I need the content ids which match color:white and type:box.
So I have 25 "white" items and 12 "box" items. So do I compare the two arrays and get the array with my "filter" result and then query the content table with these ids?

This means I need about 3 queryes
1 to get the color filter, 1 to get the type filter and 1 to get the data out of the content table.
Now this expands rapidly if I want to filter out using 5 filters so I get 5+1 query - real stress for the DB.

Can you help me out with this? :)