Faceted Search Attribute Count

Question

I want to have an attribute count query for following fiddle. FIDDLE

Its working perfect but I'm failed to count the attributes using this. Don't know how it'll be achieved because after spending many hours and changing query still its not working for me. Anyone has suggestions??

Answer 1

Here is how you can this:

SELECT meta_name, meta_value, COUNT(DISTINCT item_id) count
  FROM meta m JOIN item_meta im
    ON im.field_id = m.id
 GROUP BY meta_name, meta_value

Output:

|      META_NAME | META_VALUE | COUNT |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     1 |
|          Color |      Black |     1 |
|          Color |        Red |     1 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     1 |
|          Model |    2Series |     1 |
|          Model |    3Series |     1 |
|          Model |      Civic |     1 |

Here is SQLFiddle demo

UPDATE:

Filtered:

SELECT meta_name, meta_value, COUNT(DISTINCT item_id) count
  FROM meta m JOIN item_meta im
    ON im.field_id = m.id
 WHERE item_id IN
(
  SELECT i.id
  FROM item_meta im JOIN items i
    ON im.item_id = i.id JOIN meta m
    ON im.field_id = m.id
 GROUP BY i.id
HAVING MAX(meta_name = 'Make' AND meta_value = 'BMW') = 1
   AND MAX(meta_name = 'Car Type' AND meta_value = 'Coupe') = 1
)
 GROUP BY meta_name, meta_value;

Output:

|      META_NAME | META_VALUE | COUNT |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|          Color |      Black |     1 |
|          Color |      White |     1 |
| Interior Color |      Black |     1 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|          Model |    2Series |     1 |
|          Model |    3Series |     1 |

Here is SQLFiddle demo

UPDATE2:

Also how to handle it for same feature coming 2 times, like, i only search black and red nothing else. It is currently showing no results. but it should show both results. but if query is like bmw black and red then it should show only bmw black or red, otherwise 0 if not found

It is easy. Naturally instead of using AND in HAVING clause (a car can't be black and red at the same time) you have to use OR like so

HAVING MAX(meta_name = 'Color' AND meta_value = 'Black') = 1
    OR MAX(meta_name = 'Color' AND meta_value = 'Red')   = 1

or here is another way to express the same intent

HAVING MAX(meta_name = 'Color' AND meta_value = 'Black') +
       MAX(meta_name = 'Color' AND meta_value = 'Red') > 0

Here is SQLFiddle demo