How do I join the tables of two columns, if both of them point to the same table?

Given the following schema:

export const potentialDuplicate = pgTable(
    "PotentialDuplicate",
    {
        id: serial("id").primaryKey().notNull(),
        originalId: text("originalId").notNull(),
        duplicateId: text("duplicateId").notNull(),
    },
    (table) => ({
        unq: unique().on(table.originalId, table.duplicateId),
    }),
);

export const potentialDuplicatesRelation = relations(potentialDuplicate, ({ one }) => ({
    orginalMedia: one(media, {
        fields: [potentialDuplicate.originalId],
        references: [media.id],
        relationName: "originalMedia",
    }),
    duplicateMedia: one(media, {
        fields: [potentialDuplicate.duplicateId],
        references: [media.id],
        relationName: "duplicateMedia",
    }),
}));

export const potentialDuplicate = pgTable(
    "PotentialDuplicate",
    {
        id: serial("id").primaryKey().notNull(),
        originalId: text("originalId").notNull(),
        duplicateId: text("duplicateId").notNull(),
    },
    (table) => ({
        unq: unique().on(table.originalId, table.duplicateId),
    }),
);

export const potentialDuplicatesRelation = relations(potentialDuplicate, ({ one }) => ({
    orginalMedia: one(media, {
        fields: [potentialDuplicate.originalId],
        references: [media.id],
        relationName: "originalMedia",
    }),
    duplicateMedia: one(media, {
        fields: [potentialDuplicate.duplicateId],
        references: [media.id],
        relationName: "duplicateMedia",
    }),
}));

How do I join the media tables on the potential duplicate table? I want both of them to be present in the result. According to the docs (https://orm.drizzle.team/docs/joins#aliases--selfjoins) I can use aliased tables, but that does not seem to work. Here is my example:

    const duplicate = aliasedTable(schema.media, "duplicate");
    const original = aliasedTable(schema.media, "original");
    const x = await db
        .select()
        .from(schema.potentialDuplicate)
        .innerJoin(original, eq(original.id, schema.potentialDuplicate.originalId))
        .innerJoin(duplicate, eq(duplicate.id, schema.potentialDuplicate.duplicateId));

    const duplicate = aliasedTable(schema.media, "duplicate");
    const original = aliasedTable(schema.media, "original");
    const x = await db
        .select()
        .from(schema.potentialDuplicate)
        .innerJoin(original, eq(original.id, schema.potentialDuplicate.originalId))
        .innerJoin(duplicate, eq(duplicate.id, schema.potentialDuplicate.duplicateId));

If I try to auto complete on x I get the tables media and potential duplicate. It seems like both media tables got the name Media, which is wrong.

Drizzle ORM - Joins

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

How do I join the tables of two columns, if both of them point to the same table?