MySQL implement search based on multiple filters

From the table structure below I'd like to be able to provide search filters based on combinations of attributes:

Table: animalAttributes

id      attributeId     animalId
1       455             55
2       999             55
3       685             55
4       999             89
5       455             89
6       333             93
7       685             93
8       999             93
--------------------------------

The front end would have checkboxes e.g.

Animal options

Colour
[ ] Black      (id 685)
[x] Brown      (id 999)

Body Covering
[ ] Fur        (id 233)
[ ] Scales     (id 333)
[x] Feathers   (id 455)

I'd expect the above checked boxes to select all animals that are brown AND have feathers. I can get this data with the following query:

SELECT animalId
FROM animalAttributes
WHERE attributeId IN (999,455)
GROUP BY animalId 
HAVING COUNT(DISTINCT attributeId) = 2;

The issue I'm having is when there are multiple options selected from multiple filters e.g.

Animal options

Colour
[x] Black      (id 685)
[x] Brown      (id 999)

Body Covering
[x] Fur        (id 233)
[ ] Scales     (id 333)
[x] Feathers   (id 455)

I'd expect the above checked boxes to select all animals that are (black OR brown) AND have (fur OR feathers). I can get this data with the following query:

SELECT animalId
FROM animalAttributes
WHERE
attributeId IN (685,233) ||
attributeId开发者_如何学JAVA IN (685,455) ||
attributeId IN (999,233) ||
attributeId IN (999,455)
GROUP BY animalId 
HAVING COUNT(DISTINCT attributeId) = 2;

If I wanted to add additional filters such as "Has Tail", "Can fly", "Blood type", etc, am I right in thinking I'd need to calculate all combinations (cartesian product) and follow the same pattern as above? e.g. 5 filters, each with 1 or more options selected

attributeId IN (x,x,x,x,x) ||
attributeId IN (x,x,x,x,x) ||
attributeId IN (x,x,x,x,x) ||
...
HAVING COUNT(DISTINCT attributeId) = 5;

other tables for reference

Table: attributes

attributeId    attributeCategoryId    attribute

233            1                      Fur
333            1                      Scales
455            1                      Feathers
685            2                      Black
999            2                      Brown
-----------------------------------------------

Table: attributeCategories

attributeCategoryId    category
1                      Body covering
2                      Colour
------------------------------------

attributeId IN (685,233) ||
attributeId IN (685,455) ||
attributeId IN (999,233) ||
attributeId IN (999,455)

will be the same as if you write: attributeId IN (685,233,455,999,233)

Try:

SELECT aa.animalId
FROM animalAttributes aa
LEFT JOIN attributes a ON a.attributeId = aa.attributeId
WHERE
(aa.attributeId IN (685,99) AND a.attributeCategoryId=1) AND
(aa.attributeId IN (223,455) AND a.attributeCAtegoryId=2)
GROUP BY animalId 

Or in words: (black OR brown AND isColor) AND (fur OR feathers AND isBodyCovering)

Untestet, but you should get the idea.

You could also do this with intersections. You can write a query that generates a result set for each filter. Then, you can intersect all of the result sets to come up with a single result set that fits all filters.

The drawback of this approach is that you are doing multiple queries on the database. If performance is an issue because your application has a lot of traffic, you can do something that is memory-intensive but not process-intensive like caching the database table you are querying so that you make only one call to the database.

If I understand your issue correctly, a bitwise key may help. For example lets say that each animal can have multiple options that are binary. Eg has tail = Yes or No (0 or 1), same for Has Scales, Has Fir etc.

Then by adding all of the possible attributes as a binary set of options you can have this

Tail(Y/N) Fir(Y/N) Scales (Y/N) Brown(Y/N) 0 1 0 1

I.e this animal has no tail, has fir, has no scales, and is brown.

This key makes it easy to filter for all items matching that criteria, and new criteria can be added with no additional code changes to the query. Look at Bitwise operators to work with these keys.