Drizzle TeamDT
Drizzle Team•5mo ago
Marian

Is this concat + string_agg with groupBy correct in Drizzle?

Hi everyone,

I’m working with Drizzle ORM (PostgreSQL) and trying to generate a 
name
 field that combines the product name with all of its attribute values, like:
"T-Shirt, Red, Large".

Here’s the code I’m using:
const products = await db
  .select({
    productId: productTable.id,
    variantId: productVariantTable.id,
    name: sql<string>`concat(${productTable.name}, ', ', string_agg(${attributeValueTable.value}, ', '))`
      .mapWith(String)
      .as("name"),
    updatedAt: productTable.updatedAt
  })
  .from(productTable)
  .innerJoin(
    productVariantTable,
    eq(productTable.id, productVariantTable.productId)
  )
  .leftJoin(
    productVariantAttributeTable,
    eq(productVariantTable.id, productVariantAttributeTable.variantId)
  )
  .innerJoin(
    attributeValueTable,
    eq(productVariantAttributeTable.attributeValueId, attributeValueTable.id)
  )
  .groupBy(productVariantTable.id);

const mapped: MetadataRoute.Sitemap = products.flatMap((product) => ({
  url: getProductVariantUrl(
    slugify(product.name),
    product.productId,
    product.variantId
  ),
  lastModified: product.updatedAt,
  priority: 0.8,
  changeFrequency: "weekly"
}));


I have two main questions:
  • Is this usage of sql<string>concat(..., string_agg(...)) valid and safe in Drizzle?
  • Is the .groupBy(productVariantTable.id) correct in this context? Do I need to group by more columns (like productTable.id) to avoid SQL errors?
Was this page helpful?