How do I GROUP BY the first expression of a UNION ALL query in Knex.JS?

Question

I have a table entries linked with another table categories. There is also a column is_marked of type BOOLEAN.

The user may want to see the marked entries separately, so I wish to show the count of

• Every category
• Every marked entry

My plan was to use UNION ALL to achieve this. This is the query to fetch the category count, as well as to fetch the marked entries:

(
SELECT 
    cat.category_name AS `name`, 
    cat.category_id AS `id`, 
    COUNT(DISTINCT e.entry_id) AS `entry_count` 
FROM `categories` AS `cat` 
    INNER JOIN `entries` AS `e` USING (category_id)
GROUP BY cat.category_name
) UNION ALL (
SELECT
    "Marked Entry" AS `name`, 
    -1 AS `id`, 
    COUNT(DISTINCT e2.entry_id) AS `entry_count` 
 FROM `entries` AS `e2` 
    WHERE `e2`.`is_marked` = 1
)

Following this plan, I made this knexJS code:

const query = knex
    .select('cat.category_name AS name', 'cat.category_id AS id')
    .countDistinct('e.entry_id AS entry_count')
    .from('categories AS cat')
    .innerJoin('entries AS e', 'e.category_id', 'cat.category_id')
    .groupBy('cat.category_name');

const markedQuery = knex
    .select(knex.raw('"Marked Entry" AS `name`'), knex.raw('-1 AS `id`'))
    .countDistinct('e2.entry_id AS entry_count')
    .from('entries AS e2')
    .where('e2.is_marked', '=', 1);

query.unionAll([markedQuery]);

However, this code puts the GROUP BY statement after the UNION ALL, which results in a 1140 SQL error.

select 
    `cat`.`category_name` as `name`, 
    `cat`.`category_id` as `id`, 
    count(distinct `e`.`entry_id`) as `entry_count` 
from `categories` as `cat` 
    inner join `entries` as `e`
    on `e`.`category_id` = `cat`.`category_id`
union all
select
    "Marked Entry" AS `name`, 
    -1 AS `id`, 
    count(distinct `e2`.`entry_id`) as `entry_count` 
from `entries` as `e2`
    where `e2`.`is_marked` = ?
group by `cat`.`category_name`

How can I force the GROUP BY to apply to the first expression? Inverting the query order does fix this particular issue, but I can't guarantee that future situations can be fixed the same way.

Answer 1

You could achieve this by supplying both of your queries to the knex.unionAll() function rather than calling unionAll on your first query. Also the second argument allows you to specify that you want to add parentheses around each query like so

knex.unionAll([query, markedQuery], true);