Drizzle Team•4mo ago

Polymorphic Association

This is my current schema, coming from kysely, i thought drizzle had a way of putting a condition on the relation, is it not possible?

export const products = pgTable('products', {
    id: serial().primaryKey(),
    name: text().notNull(),
    slug: text().notNull().unique(),
    // VDE: SKU-Code, Cla-Val: Model
    model: text().notNull(),
    brand: text().notNull(),
    // VDE Product does not have a description
    description: text(),
    categoryId: integer('category_id').notNull(),
    keySpecs: json('key_specs').$type<Record<string, string>>().notNull().default({}),
    specifications: json().$type<Record<string, string>>().notNull().default({}),
    createdAt: timestamp('created_at', { precision: 3, mode: 'date' }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { precision: 3, mode: 'date' })
        .notNull()
        .defaultNow()
        .$onUpdateFn(() => new Date())
})

export const productsRelations = relations(products, ({ one, many }) => ({
    category: one(categories, {
        fields: [products.categoryId],
        references: [categories.id]
    }),
    images: many(images, {
        relationName: 'productImages',
        condition
    })
}))

export const products = pgTable('products', {
    id: serial().primaryKey(),
    name: text().notNull(),
    slug: text().notNull().unique(),
    // VDE: SKU-Code, Cla-Val: Model
    model: text().notNull(),
    brand: text().notNull(),
    // VDE Product does not have a description
    description: text(),
    categoryId: integer('category_id').notNull(),
    keySpecs: json('key_specs').$type<Record<string, string>>().notNull().default({}),
    specifications: json().$type<Record<string, string>>().notNull().default({}),
    createdAt: timestamp('created_at', { precision: 3, mode: 'date' }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { precision: 3, mode: 'date' })
        .notNull()
        .defaultNow()
        .$onUpdateFn(() => new Date())
})

export const productsRelations = relations(products, ({ one, many }) => ({
    category: one(categories, {
        fields: [products.categoryId],
        references: [categories.id]
    }),
    images: many(images, {
        relationName: 'productImages',
        condition
    })
}))

export const mediaOwnerType = pgEnum('media_owner_type', ['product', 'category'])

export const images = pgTable(
    'images',
    {
        id: serial().primaryKey(),
        url: text().notNull(),
        name: varchar({ length: 256 }).notNull(),
        position: integer().notNull().default(0),
        bytes: integer().notNull(),
        ownerId: integer('owner_id').notNull(),
        ownerType: mediaOwnerType('owner_type').notNull(),
        createdAt: timestamp('created_at', { precision: 3, mode: 'date' }).notNull().defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 3, mode: 'date' })
            .notNull()
            .defaultNow()
            .$onUpdateFn(() => new Date())
    },
    t => [index('owner_position_idx').on(t.ownerId, t.ownerType, t.position)]
)

export const mediaOwnerType = pgEnum('media_owner_type', ['product', 'category'])

export const images = pgTable(
    'images',
    {
        id: serial().primaryKey(),
        url: text().notNull(),
        name: varchar({ length: 256 }).notNull(),
        position: integer().notNull().default(0),
        bytes: integer().notNull(),
        ownerId: integer('owner_id').notNull(),
        ownerType: mediaOwnerType('owner_type').notNull(),
        createdAt: timestamp('created_at', { precision: 3, mode: 'date' }).notNull().defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 3, mode: 'date' })
            .notNull()
            .defaultNow()
            .$onUpdateFn(() => new Date())
    },
    t => [index('owner_position_idx').on(t.ownerId, t.ownerType, t.position)]
)

8 Replies

Patrik•4mo ago

wdym condition on the relation im not really sql guy but i wanna help do you know its not neccessary to create relations()? its only for query syntax which is not ideal

Mr. SealOP•4mo ago

i forgot about that

export const productsRelations = relations(productsTable, ({ many }) => ({
  images: many(imagesTable, {
    relationName: 'productImages',
    condition: (image, { eq }) => eq(image.ownerType, 'product'),
  }),
  files: many(filesTable, {
    relationName: 'productFiles',
    condition: (file, { eq }) => eq(file.ownerType, 'product'),
  }),
}));

export const productsRelations = relations(productsTable, ({ many }) => ({
  images: many(imagesTable, {
    relationName: 'productImages',
    condition: (image, { eq }) => eq(image.ownerType, 'product'),
  }),
  files: many(filesTable, {
    relationName: 'productFiles',
    condition: (file, { eq }) => eq(file.ownerType, 'product'),
  }),
}));

Patrik•4mo ago

i dont quite understand i dont think it should be in schema declaration it should be in repository or whatever you use to access database layer

Mr. SealOP•4mo ago

smth like this?

  const result = await db
    .select({
      product: productsTable,
      images: sql<Image[]>`JSON_AGG(DISTINCT ${imagesTable}) FILTER (WHERE ${imagesTable.id} IS NOT NULL)`,
      files: sql<File[]>`JSON_AGG(DISTINCT ${filesTable}) FILTER (WHERE ${filesTable.id} IS NOT NULL)`,
    })
    .from(productsTable)
    .where(eq(productsTable.id, productId))
    .leftJoin(
      imagesTable,
      and(
        eq(imagesTable.ownerId, productsTable.id),
        eq(imagesTable.ownerType, 'product')
      )
    )
    .leftJoin(
      filesTable,
      and(
        eq(filesTable.ownerId, productsTable.id),
        eq(filesTable.ownerType, 'product')
      )
    )
    .groupBy(productsTable.id);

  const result = await db
    .select({
      product: productsTable,
      images: sql<Image[]>`JSON_AGG(DISTINCT ${imagesTable}) FILTER (WHERE ${imagesTable.id} IS NOT NULL)`,
      files: sql<File[]>`JSON_AGG(DISTINCT ${filesTable}) FILTER (WHERE ${filesTable.id} IS NOT NULL)`,
    })
    .from(productsTable)
    .where(eq(productsTable.id, productId))
    .leftJoin(
      imagesTable,
      and(
        eq(imagesTable.ownerId, productsTable.id),
        eq(imagesTable.ownerType, 'product')
      )
    )
    .leftJoin(
      filesTable,
      and(
        eq(filesTable.ownerId, productsTable.id),
        eq(filesTable.ownerType, 'product')
      )
    )
    .groupBy(productsTable.id);

im not a sql guy

Patrik•4mo ago

await db
  .select()
  .from(productsTable)
  .innerJoin(imagesTable, eq(imagesTable.ownerId, productsTable.id))
  .innerJoin(filesTable, eq(filesTable.ownerId, productsTable.id))
  .where(and(
    eq(imagesTable.ownerType, 'product'),
    eq(filesTable.ownerType, 'product')
  ))

await db
  .select()
  .from(productsTable)
  .innerJoin(imagesTable, eq(imagesTable.ownerId, productsTable.id))
  .innerJoin(filesTable, eq(filesTable.ownerId, productsTable.id))
  .where(and(
    eq(imagesTable.ownerType, 'product'),
    eq(filesTable.ownerType, 'product')
  ))

smth like that i guess im not sure its right join query

Mr. SealOP•4mo ago

thanks

Patrik•4mo ago

you can actually do it like this

.leftJoin(
      imagesTable,
      and(
        eq(imagesTable.ownerId, productsTable.id),
        eq(imagesTable.ownerType, 'product')
      )
    )
    .leftJoin(
      filesTable,
      and(
        eq(filesTable.ownerId, productsTable.id),
        eq(filesTable.ownerType, 'product')
      )
    )

.leftJoin(
      imagesTable,
      and(
        eq(imagesTable.ownerId, productsTable.id),
        eq(imagesTable.ownerType, 'product')
      )
    )
    .leftJoin(
      filesTable,
      and(
        eq(filesTable.ownerId, productsTable.id),
        eq(filesTable.ownerType, 'product')
      )
    )

i did check it should work

Mr. SealOP•4mo ago

tyy

Gaming

Programming

Polymorphic Association

Did you find this page helpful?