Drizzle Team•6mo 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 ??? );

7 Replies

Justin•6mo 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.

Deleted_user_f8439ab4ed59•6mo ago

Have a great time!

Justin•6mo ago

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

НарбекOP•6mo 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•6mo 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 🙂

НарбекOP•6mo ago

👍

jingus•2mo ago

@НарбекDid you ever solve this? I'm having a similar problem. I tried using the second suggestion from @Justin , but I get an error saying my column doesn't exist.

const members = db
    .selectDistinct({
        id: channelTable.id,
        name: channelTable.name,
        description: channelTable.description,
        icon: channelTable.icon,
        public: sql`false`,
    })
    .from(channelTable)
    .leftJoin(publicChannelTable, eq(channelTable.id, publicChannelTable.channelId))
    .innerJoin(roleTable, eq(roleTable.channelId, channelTable.id))
    .innerJoin(
        userRoleTable,
        and(eq(userRoleTable.roleId, roleTable.id), eq(userRoleTable.userId, requester.id))
    )
    .where(and(ilike(channelTable.name, `%${name}%`), isNull(publicChannelTable.name)));
return (
    await union(pub, owns, members)
        .orderBy(sql`public`)
        .limit(CHANNEL_SEARCH_PAGE_SIZE)
        .offset(page * CHANNEL_SEARCH_PAGE_SIZE)
).filter((c) => c != null);

const members = db
    .selectDistinct({
        id: channelTable.id,
        name: channelTable.name,
        description: channelTable.description,
        icon: channelTable.icon,
        public: sql`false`,
    })
    .from(channelTable)
    .leftJoin(publicChannelTable, eq(channelTable.id, publicChannelTable.channelId))
    .innerJoin(roleTable, eq(roleTable.channelId, channelTable.id))
    .innerJoin(
        userRoleTable,
        and(eq(userRoleTable.roleId, roleTable.id), eq(userRoleTable.userId, requester.id))
    )
    .where(and(ilike(channelTable.name, `%${name}%`), isNull(publicChannelTable.name)));
return (
    await union(pub, owns, members)
        .orderBy(sql`public`)
        .limit(CHANNEL_SEARCH_PAGE_SIZE)
        .offset(page * CHANNEL_SEARCH_PAGE_SIZE)
).filter((c) => c != null);

pub and owns are similar queries to members. They select the same columns. Only variation comes in the public column in the select.

Gaming

Programming

How to combine 'union' and 'orderBy'