Faceted search using MySQL

user_728756 0
Viewed 183

I'm currently trying to build a MySQL (version 8) query to get a list of filters with an article count. I know I can use Elasticsearch to achieve the desired result, but the requirement is to use MySQL.

Data

DB Fiddle

Query

SELECT sf.name, sff.title, sff.key, COUNT(DISTINCT sfa.id) AS articles_count
FROM shop_filters AS sf
INNER JOIN shop_filter_facets AS sff ON sff.filter_id = sf.id
LEFT JOIN shop_facetables AS sfa ON (
    sfa.facet_id = sff.id AND sfa.facetable_id IN (
        SELECT sfa.facetable_id
        FROM shop_filter_facets AS sff
        INNER JOIN shop_facetables AS sfa ON sfa.facet_id = sff.id
        INNER JOIN shop_filters AS sf ON sf.id = sff.filter_id
        GROUP BY sfa.facetable_id
        HAVING (
            sf.name = 'filter_1' AND MAX(sff.key = 1252884110) = 1
            OR MAX(sff.key = 1741157870) = 1
        )
    )
)
GROUP BY sf.name, sff.title, sff.key

Output

Output

As you can see, the other filter_1 items have a count of 0. They should display a count higher than zero. What am I missing in the query above?

Expected output

Expected output

An example of how the faceted search should behave:

Example

mysql
1 Answers

You just needed to change an AND to an OR in the HAVING clause:

SELECT sf.name, sff.title, sff.key, COUNT(DISTINCT sfa.id) AS articles_count
FROM shop_filters AS sf
INNER JOIN shop_filter_facets AS sff ON sff.filter_id = sf.id
LEFT JOIN shop_facetables AS sfa ON (
    sfa.facet_id = sff.id AND sfa.facetable_id IN (
        SELECT sfa.facetable_id
        FROM shop_filter_facets AS sff
        INNER JOIN shop_facetables AS sfa ON sfa.facet_id = sff.id
        INNER JOIN shop_filters AS sf ON sf.id = sff.filter_id
        GROUP BY sfa.facetable_id
        HAVING (
            sf.name = 'filter_1' OR MAX(sff.key = 1252884110) = 1
            OR MAX(sff.key = 1741157870) = 1
         )
    )
)
GROUP BY sf.name, sff.title, sff.key;

The Results:

| name     | title     | key        | articles_count |
| -------- | --------- | ---------- | -------------- |
| filter_1 | Facet 1-A | 1741157870 | 5              |
| filter_1 | Facet 1-B | 9401707597 | 4              |
| filter_1 | Facet 1-C | 8395537669 | 27             |
| filter_1 | Facet 1-D | 1252884110 | 18             |
| filter_1 | Facet 1-E | 885500301  | 1              |
| filter_2 | Facet 2-A | 5454540233 | 4              |
| filter_2 | Facet 2-B | 2418516648 | 3              |
| filter_2 | Facet 2-C | 2808696733 | 4              |
| filter_2 | Facet 2-D | 8692535611 | 5              |
| filter_2 | Facet 2-E | 6389292333 | 0              |
| filter_2 | Facet 2-F | 5107586138 | 4              |
| filter_2 | Facet 2-G | 9464620325 | 3              |
| filter_2 | Facet 2-H | 1166556565 | 0              |
| filter_2 | Facet 2-I | 2739765054 | 0              |
| filter_3 | Facet 3-A | 1112385648 | 23             |
| filter_4 | Facet 4-A | 2883255908 | 2              |
| filter_4 | Facet 4-B | 1507996583 | 3              |
| filter_4 | Facet 4-C | 7632658109 | 3              |
| filter_4 | Facet 4-D | 2990697496 | 2              |
| filter_5 | Facet 5-A | 2051629771 | 16             |
| filter_5 | Facet 5-B | 6620949318 | 6              |
| filter_5 | Facet 5-C | 8962757449 | 2              |
| filter_5 | Facet 5-D | 2020077129 | 2              |

View on DB Fiddle

user_28237190
Related