T
TanStack•2w ago
rising-crimson

How can i get multiples row in a query with join

  const { data: products } = useLiveQuery((q) => {
    let products = q.from({ tried: triedsCollection })
      .innerJoin({ product: productsCollection }, ({ product, tried }) => eq(product.id, tried.productId))
      .orderBy(({ tried }) => tried.createdAt, "desc")
      .select(({ tried, product }) => ({
        id: product.id,
        name: product.name,
        upcId: product.upcId,
        note: tried.note,
        triedAt: tried.createdAt,
      }))

    return q.from({ productBrand: productBrandsCollection })
      .innerJoin({ product: products }, ({ product, productBrand }) => eq(productBrand.productId, product.id))
      .innerJoin({ brand: brandsCollection }, ({ brand, productBrand }) => eq(brand.id, productBrand.brandId))
      .fn.select(({ product, brand, productBrand }) => ({
        ...product,
        // brands: productBrand,
        brand: [brand?.name]
      }))
  })
  const { data: products } = useLiveQuery((q) => {
    let products = q.from({ tried: triedsCollection })
      .innerJoin({ product: productsCollection }, ({ product, tried }) => eq(product.id, tried.productId))
      .orderBy(({ tried }) => tried.createdAt, "desc")
      .select(({ tried, product }) => ({
        id: product.id,
        name: product.name,
        upcId: product.upcId,
        note: tried.note,
        triedAt: tried.createdAt,
      }))

    return q.from({ productBrand: productBrandsCollection })
      .innerJoin({ product: products }, ({ product, productBrand }) => eq(productBrand.productId, product.id))
      .innerJoin({ brand: brandsCollection }, ({ brand, productBrand }) => eq(brand.id, productBrand.brandId))
      .fn.select(({ product, brand, productBrand }) => ({
        ...product,
        // brands: productBrand,
        brand: [brand?.name]
      }))
  })
i want to get all brands associated with my product
2 Replies
rising-crimson
rising-crimsonOP•2w ago
.select(({ product, brand, productBrand }) => ({
  ...product,
  brands: q.from({ brand: brandsCollection })
    .innerJoin({ productBrands }, ({ brand, productBrands }) => eq(productBrands.productBrand.productId, brand.id))
    .where(({ productBrands }) => eq(productBrands.product.id, product.id))
}))
.select(({ product, brand, productBrand }) => ({
  ...product,
  brands: q.from({ brand: brandsCollection })
    .innerJoin({ productBrands }, ({ brand, productBrands }) => eq(productBrands.productBrand.productId, brand.id))
    .where(({ productBrands }) => eq(productBrands.product.id, product.id))
}))
something like that. like in sql 
(
  SELECT string_agg(b.name, ', ' ORDER BY pb.order)
  FROM "ProductBrand" pb
  JOIN "Brand" b ON pb."brandId" = b.id
  WHERE pb."productId" = p.id
) AS brands,
(
  SELECT string_agg(b.name, ', ' ORDER BY pb.order)
  FROM "ProductBrand" pb
  JOIN "Brand" b ON pb."brandId" = b.id
  WHERE pb."productId" = p.id
) AS brands,
rising-crimson
rising-crimsonOP•2w ago
https://github.com/TanStack/db/issues/288
GitHub
Joins with a hierarchical projection (includes) · Issue #288 · Ta...
The data shown in a user interface if often hierarchical (projects -> issues -> comments) The joins supported by Tanstack DB are SQL like joins where the resulting rows are flat, however user...

Did you find this page helpful?