title: MySQL implement search based on multiple filters
body: <p>From the table structure below I'd like to be able to provide search filters based on combinations of attributes:</p>
  
  <p><strong>Table: animalAttributes</strong></p>
  
  <pre><code>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
  --------------------------------
  </code></pre>
  
  <p>The front end would have checkboxes e.g.</p>
  
  <pre><code>Animal options
  
  Colour
  [ ] Black      (id 685)
  [x] Brown      (id 999)
  
  Body Covering
  [ ] Fur        (id 233)
  [ ] Scales     (id 333)
  [x] Feathers   (id 455)
  </code></pre>
  
  <p>I'd expect the above checked boxes to select all animals that are brown <strong>AND</strong> have feathers. I can get this data with the following query:</p>
  
  <pre><code>SELECT animalId
  FROM animalAttributes
  WHERE attributeId IN (999,455)
  GROUP BY animalId 
  HAVING COUNT(DISTINCT attributeId) = 2;
  </code></pre>
  
  <p>The issue I'm having is when there are multiple options selected from multiple filters e.g.</p>
  
  <pre><code>Animal options
  
  Colour
  [x] Black      (id 685)
  [x] Brown      (id 999)
  
  Body Covering
  [x] Fur        (id 233)
  [ ] Scales     (id 333)
  [x] Feathers   (id 455)
  </code></pre>
  
  <p>I'd expect the above checked boxes to select all animals that are (black <strong>OR</strong> brown) <strong>AND</strong> have (fur <strong>OR</strong> feathers). I can get this data with the following query:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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 (<a href="https://stackoverflow.com/questions/2516599/php-2d-array-output-all-combinations">cartesian product</a>) and follow the same pattern as above? e.g. 5 filters, each with 1 or more options selected</p>
  
  <pre><code>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;
  </code></pre>
  
  <p><strong>other tables for reference</strong></p>
  
  <p><strong>Table: attributes</strong></p>
  
  <pre><code>attributeId    attributeCategoryId    attribute
  
  233            1                      Fur
  333            1                      Scales
  455            1                      Feathers
  685            2                      Black
  999            2                      Brown
  -----------------------------------------------
  </code></pre>
  
  <p><strong>Table: attributeCategories</strong></p>
  
  <pre><code>attributeCategoryId    category
  1                      Body covering
  2                      Colour
  ------------------------------------
  </code></pre>