how to do the opposite of inArray()?

i'm getting all collections (music single, ep, or album) that contain a certain tag. currently i'm doing this:

const chosenTagCollectionIDs = tagMap.get(tag);

    const collections = await getCollections({
        where: inArray(collectionsTable.id, chosenTagCollectionIDs),
        sort: true
    });

const chosenTagCollectionIDs = tagMap.get(tag);

    const collections = await getCollections({
        where: inArray(collectionsTable.id, chosenTagCollectionIDs),
        sort: true
    });

where chosenTagCollectionIDs is a list of collection ids that have the tag i'm looking for. collectionsTable.tags is a list of tags. can i somehow use inArray() to check if the collectionsTable.tags array contains tag?

5 Replies

paaradisoOP•14mo ago

and this is what getCollections() looks like:

export async function getCollections({ skip = 0, limit = 0, sort = true, where } = {}) {
    const collections = await db
        .select({
            ...getTableColumns(collectionsTable),
            releaseDate: sql`to_char(release_date, 'YYYY-MM-DD')`
        })
        .from(collectionsTable)
        .where(where)
        .orderBy(
            ...(sort
                ? [asc(sql`lower(${collectionsTable.artist})`), asc(sql`lower(${collectionsTable.name})`)]
                : [])
        )
        .limit(limit)
        .offset(skip);
    return collections;
}

export async function getCollections({ skip = 0, limit = 0, sort = true, where } = {}) {
    const collections = await db
        .select({
            ...getTableColumns(collectionsTable),
            releaseDate: sql`to_char(release_date, 'YYYY-MM-DD')`
        })
        .from(collectionsTable)
        .where(where)
        .orderBy(
            ...(sort
                ? [asc(sql`lower(${collectionsTable.artist})`), asc(sql`lower(${collectionsTable.name})`)]
                : [])
        )
        .limit(limit)
        .offset(skip);
    return collections;
}

Luxaritas•14mo ago

How are you storing tags?

paaradisoOP•14mo ago

just an array

export const collectionsTable = pgTable('collections', {
    id: integer('id').primaryKey(),
    name: text('name'),
    type: collectionTypeEnum('type'),
    cover: text('cover'),
    artist: text('artist'),
    tags: text('tags')
        .references(() => tagsTable.name)
        .array(),
    releaseDate: date('release_date'),
    addedBy: text('added_by')
});

export const collectionsTable = pgTable('collections', {
    id: integer('id').primaryKey(),
    name: text('name'),
    type: collectionTypeEnum('type'),
    cover: text('cover'),
    artist: text('artist'),
    tags: text('tags')
        .references(() => tagsTable.name)
        .array(),
    releaseDate: date('release_date'),
    addedBy: text('added_by')
});

Luxaritas•14mo ago

I'm not a postgres user, but from what I can tell you'd need the ANY function, which drizzle doesn't have built-in. So the condition would be something like

eq(tag, sql`ANY(${collectionsTable.tags})`)

eq(tag, sql`ANY(${collectionsTable.tags})`)

paaradisoOP•14mo ago

that works, thanks 🙂

Gaming

Programming

how to do the opposite of inArray()?