DT
Drizzle Team•2y ago
Shubham-Sinha

How to fetch related table rows in many_to_many

I have three tables : products, collections and collections_products. Product can belong to many collections and collection can have many products. 
const result = await db.query.products.findMany({
    with: {
      store: true,
      collection: true,
    },
    orderBy: desc(products.displayScore)
  })
const result = await db.query.products.findMany({
    with: {
      store: true,
      collection: true,
    },
    orderBy: desc(products.displayScore)
  })
When using 👆 I get this error: could not identify an equality operator for type json schema.ts 
const products = pgTable(
  "products",
  {
    ...id,
    title: varchar("title").notNull(),
    storeId: cuid("store_id").references(() => stores.id, {
      onDelete: "cascade",
    })
  },
  products => ({
    primary: primaryKey(products.id)
  })
);

const productRelations = relations(products, ({ many, one }) => ({
  collectionsProducts: many(collectionsProducts),
  store: one(stores, {
           fields: [collections.storeId],
           references: [stores.id],
         }),
}));

const collections = pgTable(
  "collections",
  {
    ...id,
    name: varchar("name", { length: 50 }).notNull(),
  },
  collections => ({
    primary: primaryKey(collections.id),

  })
);

const collectionsRelations = relations(collections, ({ many }) => ({
  collectionsProducts: many(collectionsProducts),
}));

const collectionsProducts = pgTable(
  "collections_products",
  {
    collectionId: cuid("collection_id")
      .notNull()
      .references(() => collections.id, { onDelete: "cascade" }),
    productId: cuid("product_id")
      .notNull()
      .references(() => products.id, { onDelete: "cascade" }),
  },
  t => ({
    pk: primaryKey(t.collectionId, t.productId),
  })
);

const collectionsProductsRelations = relations(
  collectionsProducts,
  ({ one }) => ({
    collection: one(collections, {
      fields: [collectionsProducts.collectionId],
      references: [collections.id],
    }),
    product: one(products, {
      fields: [collectionsProducts.productId],
      references: [products.id],
    }),
  })
);
const products = pgTable(
  "products",
  {
    ...id,
    title: varchar("title").notNull(),
    storeId: cuid("store_id").references(() => stores.id, {
      onDelete: "cascade",
    })
  },
  products => ({
    primary: primaryKey(products.id)
  })
);

const productRelations = relations(products, ({ many, one }) => ({
  collectionsProducts: many(collectionsProducts),
  store: one(stores, {
           fields: [collections.storeId],
           references: [stores.id],
         }),
}));

const collections = pgTable(
  "collections",
  {
    ...id,
    name: varchar("name", { length: 50 }).notNull(),
  },
  collections => ({
    primary: primaryKey(collections.id),

  })
);

const collectionsRelations = relations(collections, ({ many }) => ({
  collectionsProducts: many(collectionsProducts),
}));

const collectionsProducts = pgTable(
  "collections_products",
  {
    collectionId: cuid("collection_id")
      .notNull()
      .references(() => collections.id, { onDelete: "cascade" }),
    productId: cuid("product_id")
      .notNull()
      .references(() => products.id, { onDelete: "cascade" }),
  },
  t => ({
    pk: primaryKey(t.collectionId, t.productId),
  })
);

const collectionsProductsRelations = relations(
  collectionsProducts,
  ({ one }) => ({
    collection: one(collections, {
      fields: [collectionsProducts.collectionId],
      references: [collections.id],
    }),
    product: one(products, {
      fields: [collectionsProducts.productId],
      references: [products.id],
    }),
  })
);
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?