Drizzle Team•6mo ago

Best way to get all unique strings from string[] column

I have a column listings.tags which is a postgres string array. I want a query to find all unique strings that match a query. Is there a better or even more drizzle-y way to do this vs. my current code:

  const tagsSubquery = db
    .select({
      tag: sql<string>`UNNEST(${listings.tags})`.as('tag')
    })
    .from(listings)
    .where(eq(listings.marketplaceIntegrationId, marketplaceIntegrationId))
    .as('tags');
  const [{ tags }] = await db
    .select({
      tags: sql<SearchTagsResponse>`ARRAY_AGG(DISTINCT ${tagsSubquery.tag})`
    })
    .from(tagsSubquery)
    .where(
      and(
        // Not using ilike function here because it causes a type error https://github.com/drizzle-team/drizzle-orm/issues/2395
        sql`${tagsSubquery.tag} ilike ${`%${searchTerm}%`}`,
        excludedTags?.length ? notInArray(tagsSubquery.tag, excludedTags) : undefined
      )
    )
    .limit(10);

  const tagsSubquery = db
    .select({
      tag: sql<string>`UNNEST(${listings.tags})`.as('tag')
    })
    .from(listings)
    .where(eq(listings.marketplaceIntegrationId, marketplaceIntegrationId))
    .as('tags');
  const [{ tags }] = await db
    .select({
      tags: sql<SearchTagsResponse>`ARRAY_AGG(DISTINCT ${tagsSubquery.tag})`
    })
    .from(tagsSubquery)
    .where(
      and(
        // Not using ilike function here because it causes a type error https://github.com/drizzle-team/drizzle-orm/issues/2395
        sql`${tagsSubquery.tag} ilike ${`%${searchTerm}%`}`,
        excludedTags?.length ? notInArray(tagsSubquery.tag, excludedTags) : undefined
      )
    )
    .limit(10);

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Best way to get all unique strings from string[] column