Self referencing composite primary key table
I'm trying to define a table where each organization can define a parent-child set of locations. Each location has a composite primary key (org, id) but i can't seem to get the self referencing foreign keys to work. Is there a way to accomplish what i'm trying to do? here's my code. Thanks!
export const locations = pgTable(
'locations',
{
id: varchar('id', { length: 60 }).notNull(),
orgId: varchar('org_id', { length: 60 }).notNull(),
name: varchar('name', { length: 255 }).notNull(),
isLeaf: boolean('is_leaf').default(false).notNull(),
parentId: varchar('parent_id', { length: 60 }),
parentOrgId: varchar('parent_org_id', { length: 60 }),
},
(table) => ({
pk: primaryKey({
columns: [table.id, table.orgId],
name: 'locations_pkey'
}),
}),
);
export const locationsRelations = relations(locations, ({ many, one }) => ({
parent: one(locations, {
fields: [locations.parentId, locations.parentOrgId],
references: [locations.id, locations.orgId],
relationName: 'parent_child',
}),
children: many(locations, {
relationName: 'parent_child',
}),
}));
export const locations = pgTable(
'locations',
{
id: varchar('id', { length: 60 }).notNull(),
orgId: varchar('org_id', { length: 60 }).notNull(),
name: varchar('name', { length: 255 }).notNull(),
isLeaf: boolean('is_leaf').default(false).notNull(),
parentId: varchar('parent_id', { length: 60 }),
parentOrgId: varchar('parent_org_id', { length: 60 }),
},
(table) => ({
pk: primaryKey({
columns: [table.id, table.orgId],
name: 'locations_pkey'
}),
}),
);
export const locationsRelations = relations(locations, ({ many, one }) => ({
parent: one(locations, {
fields: [locations.parentId, locations.parentOrgId],
references: [locations.id, locations.orgId],
relationName: 'parent_child',
}),
children: many(locations, {
relationName: 'parent_child',
}),
}));
2 Replies
Hi, can you provide the error you are getting?
It seems like your schema is right, I've got it working on my machine.
I also made a script to test it, and everything works:
results in:
const cozy = {
locations: [
{
id: "loc1",
orgId: "org1",
name: "Headquarters",
isLeaf: false,
parentId: null,
parentOrgId: null,
},
{
id: "loc2",
orgId: "org1",
name: "Branch Office 1",
isLeaf: false,
parentId: "loc1",
parentOrgId: "org1",
},
{
id: "loc3",
orgId: "org1",
name: "Branch Office 2",
isLeaf: false,
parentId: "loc1",
parentOrgId: "org1",
},
{
id: "loc4",
orgId: "org1",
name: "Storage Facility",
isLeaf: true,
parentId: "loc2",
parentOrgId: "org1",
},
{
id: "loc5",
orgId: "org2",
name: "Regional HQ",
isLeaf: false,
parentId: null,
parentOrgId: null,
},
{
id: "loc6",
orgId: "org2",
name: "Warehouse",
isLeaf: true,
parentId: "loc5",
parentOrgId: "org2",
},
],
};
await db
.insert(locations)
.values(cozy.locations)
.catch((err) => {
console.error(err);
process.exit(1);
});
const result = await db
.select()
.from(locations)
.where(isNull(locations.parentId));
console.log(result);
const childrenTable = aliasedTable(locations, "children");
const withChildren = await db
.select({
parentId: locations.id,
childrenIds: sql`array_agg(children.id)`,
})
.from(locations)
.innerJoin(
childrenTable,
and(
eq(childrenTable.parentId, locations.id),
eq(childrenTable.parentOrgId, locations.orgId),
),
)
.groupBy(locations.id);
console.log(withChildren);
const cozy = {
locations: [
{
id: "loc1",
orgId: "org1",
name: "Headquarters",
isLeaf: false,
parentId: null,
parentOrgId: null,
},
{
id: "loc2",
orgId: "org1",
name: "Branch Office 1",
isLeaf: false,
parentId: "loc1",
parentOrgId: "org1",
},
{
id: "loc3",
orgId: "org1",
name: "Branch Office 2",
isLeaf: false,
parentId: "loc1",
parentOrgId: "org1",
},
{
id: "loc4",
orgId: "org1",
name: "Storage Facility",
isLeaf: true,
parentId: "loc2",
parentOrgId: "org1",
},
{
id: "loc5",
orgId: "org2",
name: "Regional HQ",
isLeaf: false,
parentId: null,
parentOrgId: null,
},
{
id: "loc6",
orgId: "org2",
name: "Warehouse",
isLeaf: true,
parentId: "loc5",
parentOrgId: "org2",
},
],
};
await db
.insert(locations)
.values(cozy.locations)
.catch((err) => {
console.error(err);
process.exit(1);
});
const result = await db
.select()
.from(locations)
.where(isNull(locations.parentId));
console.log(result);
const childrenTable = aliasedTable(locations, "children");
const withChildren = await db
.select({
parentId: locations.id,
childrenIds: sql`array_agg(children.id)`,
})
.from(locations)
.innerJoin(
childrenTable,
and(
eq(childrenTable.parentId, locations.id),
eq(childrenTable.parentOrgId, locations.orgId),
),
)
.groupBy(locations.id);
console.log(withChildren);
[
{
id: "loc1",
orgId: "org1",
name: "Headquarters",
isLeaf: false,
parentId: null,
parentOrgId: null,
}, {
id: "loc5",
orgId: "org2",
name: "Regional HQ",
isLeaf: false,
parentId: null,
parentOrgId: null,
}
]
[
{
parentId: "loc1",
childrenIds: [ "loc2", "loc3" ],
}, {
parentId: "loc2",
childrenIds: [ "loc4" ],
}, {
parentId: "loc5",
childrenIds: [ "loc6" ],
}
]
[
{
id: "loc1",
orgId: "org1",
name: "Headquarters",
isLeaf: false,
parentId: null,
parentOrgId: null,
}, {
id: "loc5",
orgId: "org2",
name: "Regional HQ",
isLeaf: false,
parentId: null,
parentOrgId: null,
}
]
[
{
parentId: "loc1",
childrenIds: [ "loc2", "loc3" ],
}, {
parentId: "loc2",
childrenIds: [ "loc4" ],
}, {
parentId: "loc5",
childrenIds: [ "loc6" ],
}
]
Thanks for your reply! interesting.. i was using the drizzle studio UI to check the relationships and it kept showing the same entity instead of the parent / child reference, so i thought it wasn't working properly. Looks like it may just be an issue with drizzle studio rather that postgres itself.