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
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.
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);