MySQL implement search based on multiple filters

Question

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 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
------------------------------------

It would really help to have some test data and expected query output, that way we could try various queries until we got the right answer. — Bohemian, Jun 02 '11 at 00:42

Rufinus · Answer 1 · 2011-06-01T23:17:21.243

1

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.

edited Jun 01 '11 at 23:17

answered Jun 01 '11 at 23:12

Rufinus

29,200
6
68
84

But how do you do this more generally? What if you added another category - then you'd have to rewrite your query. There must be a query that allows new attribute data to be added without changing the query – Bohemian Jun 02 '11 at 00:41
if you extend your house with a new room you will have to build a door in your old house. – Rufinus Jun 02 '11 at 04:54
`a.attributeCategoryId` is unlikely to be both `1` and `2` at the same time. – Andriy M Jun 02 '11 at 11:57
Actually, it now seems like you've almost made it. You should probably just change the 'main' `AND`, i.e. `WHERE (…) **AND** (…)`, to `OR` and add `HAVING COUNT(DISTINCT a.attributeCategoryId) = 2`. – Andriy M Jun 02 '11 at 12:34

score 1 · Answer 2 · answered Jun 06 '11 at 16:50

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.

score 1 · Answer 3 · answered Jun 14 '11 at 16:11

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.

MySQL implement search based on multiple filters

3 Answers3