DT
Drizzle Team•3mo 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"
}));
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?
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?