Drizzle Team•3mo ago

How to combine 'union' and 'orderBy'

const a = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const b = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);
const result = await unionAll(a, b).orderBy( across resulting rows ??? );

const a = db.select({ transaction: onlineSales.transactionId }).from(onlineSales);
const b = db.select({ transaction: inStoreSales.transactionId }).from(inStoreSales);
const result = await unionAll(a, b).orderBy( across resulting rows ??? );

6 Replies

Justin•3mo ago

Personally I would create an alias of the union, and then create your orderBy with the alias variable. It makes the code a bit more clear as to what you’re doing. I’m currently heading to the airport at the moment, so if you can’t solve it within that bit of info, let me know and I’ll give you an example after I land.

mbe52are_93191•3mo ago

Have a great time!

Justin•3mo ago

Did you still need help with this, or did you figure it out? 🙂

Нарбек•3mo ago

I had to make completely different set of requests as a workaround. No idea how to use aliases to get it work( so i've used sort method anyway. It's gonna block the event loop i guess

Justin•3mo ago

So I just had a play with it, and unfortunately alias was not the answer I had thought it would be. You would need to do something like this:

import { sql } from 'drizzle-orm';
// insert your other code here...
  const result = await unionAll(a, b).orderBy(sql`transactionId`);

import { sql } from 'drizzle-orm';
// insert your other code here...
  const result = await unionAll(a, b).orderBy(sql`transactionId`);

I was originally hoping to wrap the unionAll in an alias(unionAll, 'myUnion'), but unfortunately it did not work as I had thought at the time. But the sql operator solves your problem 🙂

Нарбек•3mo ago

👍

Gaming

Programming

How to combine 'union' and 'orderBy'