knex: group by problems in union query

Hi all, I need to perform 2 group by in the inner query of an union query

this is my code

return bookshelf
        .knex('lists')
        // first half of the query, we just take
        // the list that the user is owner, we need to
        // have the field correctly
        .select('lists.id', 'lists.name', 'lists.public',
          'lists.editable', 'lists.owner_id', 'lists.created_at')
        .count('list_items.id as item_counter')
        .from('lists')
        .leftJoin('list_items', 'list.id', 'list_items.id')
        .where('lists.owner_id', user_id)
        .groupBy('list_members.list_id')
        // we just need to group by list_id
        //.groupBy('list_members.list_id')
        // second half of the query, we need to retrive
        // the list that the user is member, this works because
        // an admin is not a member
        .union(function(){
          this.select('lists.id', 'lists.name', 'lists.public',
            'lists.editable', 'lists.owner_id', 'lists.created_at')
          //.count('list_members.user_id as member_counter')
          .count('list_items.id as item_counter')
          .from('lists')
          .innerJoin('list_members', 'lists.id', 'list_members.list_id')
          .leftJoin('list_items', 'lists_id', 'lists_items.list_id')
          //.where('lists_members.user_id', user_id)
          .groupBy('list_members.list_id');
        });

the result query is

 select `lists`.`id`, `lists`.`name`, `lists`.`public`, `lists`.`editable`, `lists`.`owner_id`, `lists`.`created_at`, count(`list_items`.`id`) as `item_counter` from `lists` left join `list_items` on `list`.`id` = `list_items`.`id` where `lists`.`owner_id` = 1 union select `lists`.`id`, `lists`.`name`, `lists`.`public`, `lists`.`editable`, `lists`.`owner_id`, `lists`.`created_at`, count(`list_items`.`id`) as `item_counter` from `lists` inner join `list_members` on `lists`.`id` = `list_members`.`list_id` left join `list_items` on `lists_id` = `lists_items`.`list_id` group by `list_members`.`list_id` group by `list_members`.`list_id` - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by `list_members`.`list_id`' at line 1
  Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by `list_members`.`list_id`' at line 1

the 2 group by are together in the last half of the query

About this issue

Original URL
State: closed
Created 9 years ago
Reactions: 1
Comments: 34 (5 by maintainers)

Most upvoted comments

I faced this problem to and one workaround that worked for me was this. From the above example by @spruce-bruce:

select * from `table`
group by `some_field`
union
select * form `table`
group by `some_other_field`

knex.from(function () {
   this.select('*').from('table').groupBy('some_field');
), true).union(function () {
   this.select('*').from('table').groupBy('some_other_field');
}, true).as('some_alias');

The true flag adds parentheses around the subquery. This also works for .unionAll(). Hope this helps!

wilsontgh on Nov 17, 2016

@pmalouin knex also knows how to automatically combine multiple query builders when they are passed as bindings to raw like this:

knex.raw(queryBuilders.map(qb => '(?)').join('UNION ALL'), queryBuilders);

elhigu on Oct 5, 2017

I came here after encountering problems with combined UNION ALL and ORDER BY. Simply reordering the components array at https://github.com/tgriesser/knex/blob/0.13.0/src/query/compiler.js#L32 did not produce acceptable results.

Here is the code (inspired from the project’s unit tests at https://github.com/tgriesser/knex/blob/0.13.0/test/unit/query/builder.js#L950):

var chain = qb().select('*').from('users').where('id', '=', 1).orderBy('id').limit(1).unionAll(function() {
  this.select('*').from('users').where('id', '=', 2);
});

Produces the following output right now:

select * from `users` where `id` = ? union all select * from `users` where `id` = ? order by `id` asc limit ?

Which is not right because I expected the ORDER BY and LIMIT clauses to apply to the first query.

After re-ordering the components, the ORDER BY and LIMIT clauses are at the right position:

select * from `users` where `id` = ? order by `id` asc limit ? union all select * from `users` where `id` = ?

Unfortunately, this query is still not valid at least for MySQL and PostgreSQL. As indicated in the MySQL documentation, the queries need to be wrapped in parenthesis. The next step was to try the wrap argument to .unionAll():

var chain = qb().select('*').from('users').where('id', '=', 1).orderBy('id').limit(1).unionAll(function() {
  this.select('*').from('users').where('id', '=', 2);
}, true);

and it produced the following result:

select * from `users` where `id` = ? order by `id` asc limit ? union all (select * from `users` where `id` = ?)

Which still has not an acceptable syntax because both the first and second queries need to be wrapped in parenthesis. So we might need to alter the behavior of the wrap argument to wrap both queries in order to use this feature.

Unfortunately, I had to fallback to raw queries for my needs. Here is a short code sample that combines an array of query builders together with UNION ALL statements between them:

const sqlExpressions = [];
let bindings = [];
queryBuilders.forEach(q => {
    const sql = q.toSQL();
    sqlExpressions.push('(' + sql.sql + ')');
    bindings = bindings.concat(sql.bindings);
});
const sql = sqlExpressions.join('UNION ALL');
return knex.raw(sql, bindings);

pmalouin on Oct 5, 2017

So the issue is that because 'group', 'having', 'order', 'limit', 'offset', 'lock' happen AFTER the union is processed we can never do a query like this:

select * from `table`
group by `some_field`
union
select * form `table`
group by `some_other_field`

same with using having, order, limit or offset in the first query. I don’t know much about how knex works, but I suppose that the only reason a PR doesn’t exist is because the above posters aren’t sure about the ramifications of changing the order of the components array.

I’ve checked out the repo and I’m going to see if tests pass with the components order changed…

spruce-bruce on Aug 16, 2016