knex subquery with union and limit

user_4806797 0
Viewed 4182

I'm trying to do the following query:

select *
from A
where id in (
    (select id from A where created_at >= '2018-12-25' order by created_at asc limit 1)
    union
    (select id from A where created_at < '2018-12-26' order by created_at desc limit 1)
)

What I've tried with knex was:

knex.select('*')
  .from('A')
  .whereIn('id', qb => 
    qb.select('*')
      .from('A')
      .where('created_at', '>=', '2018-12-25')
      .orderBy('created_at', 'ASC')
      .limit(1)
      .union(qb =>
        qb.select('*')
          .from('A')
          .where('created_at', '<', '2018-12-26')
          .orderBy('created_at', 'DESC')
          .limit(1)
      )
  )

But it yields different SQL:

select *
from "A"
where "id" in (select * from "A" where "created_at" >= ? union select * from "A" where "created_at" < ? order by "created_at" DESC limit ? order by "created_at" ASC limit ?)

Seems like order by clauses are not processed in the way I want, also, parentheses groups are not the same. What is my mistake here? How do I do it right with knex?

knex.js postgresql sql
1 Answers

Following will output your desired sql but I guess its not the efficient ways to doing thing.

const sql = knex.select('*')
   .from('A')
   .whereIn('id', qb =>
      qb.union(qb =>
            qb.select('*')
               .from('A')
               .where('created_at', '>=', '2018-12-25')
               .orderBy('created_at', 'ASC')
               .limit(1)
         )
         .union(qb =>
            qb.select('*')
               .from('A')
               .where('created_at', '<', '2018-12-26')
               .orderBy('created_at', 'DESC')
               .limit(1)
         , false)
   )
   .toSQL();
console.log(sql) ;
user_32969280
Related