Based on movie_table from faceting playground Manticore Faceting
Suppose we want to show search results like best movies by year
select id, movie_title, title_year, imdb_score, GROUP_CONCAT(id) as ids from movies WHERE id < 5 group by title_year WITHIN GROUP ORDER BY imdb_score DESC order by title_year asc FACET title_year ORDER BY COUNT(*) DESC LIMIT 3; SHOW META;
The search results are ok, we see 3 results (ids is not needed field, only to show grouping is working)
------+-------------+------------+------------+------+
| id | movie_title | title_year | imdb_score | ids |
+------+-------------+------------+------------+------+
| 2 | 11:14 | 2003 | 7.200000 | 2 |
| 4 | 9 | 2009 | 7.100000 | 1,4 |
| 3 | 3 | 2010 | 6.800000 | 3 |
+------+-------------+------------+------------+------+
, but facets are not - they show 4 records
+------------+----------+
| title_year | count(*) |
+------------+----------+
| 2009 | 2 |
| 2003 | 1 |
| 2010 | 1 |
+------------+----------+
Looks like facets, as soon as it is grouping itself, does not use grouping which is in base query. I would suppose facets to group visible result set from parent query, but it does not. Is there a way to use facet grouping on top of parent query group by?