jingus Comments - Answer Overflow

jingus

•Created by Нарбек on 6/27/2024 in #help

How to combine 'union' and 'orderBy'

@Нарбек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.

9 replies

Gaming

Programming