Incorrect relations
I have this Drizzle ORM query:
The problem is that "members" under each project.entity is empty, even though the correct rows and references are in the database. You can see how I have defined my schemas in my comment on this post.
The weird thing is, if I change the relation name to "entity" like so:
Suddenly I do get the correct result. How can this be? The relation name should be "member", because I want to fetch the nested members. Or am I misunderstanding?
Hopefully you can help me out!
const projects = await ctx.db.query.syndicationProject.findMany({
with: {
entity: {
with: {
members: {
with: {
member: true,
},
},
},
},
},
});
const projects = await ctx.db.query.syndicationProject.findMany({
with: {
entity: {
with: {
members: {
with: {
member: true,
},
},
},
},
},
});
export const personRelations = relations(person, ({ one, many }) => ({
members: many(membership, { relationName: "entity" }),
}));
export const personRelations = relations(person, ({ one, many }) => ({
members: many(membership, { relationName: "entity" }),
}));
1 Reply
Here are the schemas and relations in code:
For debugging purposes, this is the SQL query that ws executed by Drizzle:
This is the query I wrote in typescript that produced the SQL query:
I filtered some columns, as you can see, to make debugging a bit easier. Hopefully someone can help me out, because I do not understand why the problem I described in my post is happening.
export const person = pgTable(
"person",
{
id: uuid("id").defaultRandom().notNull().primaryKey(),
type: text("type", {
enum: ["natural-person", "entity"],
}).notNull(),
emailAddress: text("email_address"),
phoneNumber: text("phone_number"),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
}
);
export const personRelations = relations(person, ({ one, many }) => ({
project: one(syndicationProject),
members: many(membership, { relationName: "member" }),
}));
export const membership = pgTable("membership", {
id: uuid("id").defaultRandom().notNull().primaryKey(),
entityId: uuid("entity_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
memberId: uuid("member_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
});
export const membershipRelations = relations(membership, ({ one }) => ({
entity: one(person, {
relationName: "entity",
fields: [membership.entityId],
references: [person.id],
}),
member: one(person, {
relationName: "member",
fields: [membership.memberId],
references: [person.id],
}),
}));
export const person = pgTable(
"person",
{
id: uuid("id").defaultRandom().notNull().primaryKey(),
type: text("type", {
enum: ["natural-person", "entity"],
}).notNull(),
emailAddress: text("email_address"),
phoneNumber: text("phone_number"),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
}
);
export const personRelations = relations(person, ({ one, many }) => ({
project: one(syndicationProject),
members: many(membership, { relationName: "member" }),
}));
export const membership = pgTable("membership", {
id: uuid("id").defaultRandom().notNull().primaryKey(),
entityId: uuid("entity_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
memberId: uuid("member_id")
.notNull()
.references(() => person.id, { onDelete: "cascade" }),
createdAt: timestamp("created_at", { withTimezone: true })
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", { withTimezone: true })
.$onUpdate(() => new Date())
.notNull(),
});
export const membershipRelations = relations(membership, ({ one }) => ({
entity: one(person, {
relationName: "entity",
fields: [membership.entityId],
references: [person.id],
}),
member: one(person, {
relationName: "member",
fields: [membership.memberId],
references: [person.id],
}),
}));
select "syndicationProject"."id", "syndicationProject_entity"."data" as "entity" from "syndication_project" "syndicationProject" left join lateral (select json_build_array("syndicationProject_entity"."id", "syndicationProject_entity_members"."data") as "data" from (select * from "person" "syndicationProject_entity" where "syndicationProject_entity"."id" = "syndicationProject"."entity_id" limit $1) "syndicationProject_entity" left join lateral (select coalesce(json_agg(json_build_array("syndicationProject_entity_members"."id", "syndicationProject_entity_members"."member_id", "syndicationProject_entity_members"."is_control_person", "syndicationProject_entity_members_member"."data")), '[]'::json) as "data" from "membership" "syndicationProject_entity_members" left join lateral (select json_build_array("syndicationProject_entity_members_member"."id") as "data" from (select * from "person" "syndicationProject_entity_members_member" where "syndicationProject_entity_members_member"."id" = "syndicationProject_entity_members"."member_id" limit $2) "syndicationProject_entity_members_member") "syndicationProject_entity_members_member" on true where "syndicationProject_entity_members"."member_id" = "syndicationProject_entity"."id") "syndicationProject_entity_members" on true) "syndicationProject_entity" on true
select "syndicationProject"."id", "syndicationProject_entity"."data" as "entity" from "syndication_project" "syndicationProject" left join lateral (select json_build_array("syndicationProject_entity"."id", "syndicationProject_entity_members"."data") as "data" from (select * from "person" "syndicationProject_entity" where "syndicationProject_entity"."id" = "syndicationProject"."entity_id" limit $1) "syndicationProject_entity" left join lateral (select coalesce(json_agg(json_build_array("syndicationProject_entity_members"."id", "syndicationProject_entity_members"."member_id", "syndicationProject_entity_members"."is_control_person", "syndicationProject_entity_members_member"."data")), '[]'::json) as "data" from "membership" "syndicationProject_entity_members" left join lateral (select json_build_array("syndicationProject_entity_members_member"."id") as "data" from (select * from "person" "syndicationProject_entity_members_member" where "syndicationProject_entity_members_member"."id" = "syndicationProject_entity_members"."member_id" limit $2) "syndicationProject_entity_members_member") "syndicationProject_entity_members_member" on true where "syndicationProject_entity_members"."member_id" = "syndicationProject_entity"."id") "syndicationProject_entity_members" on true) "syndicationProject_entity" on true
const projects = await ctx.db.query.syndicationProject.findMany({
columns: { id: true },
with: {
entity: {
columns: { id: true },
with: {
members: {
columns: {
id: true,
memberId: true,
isControlPerson: true,
},
with: {
member: { columns: { id: true } },
},
},
},
},
},
});
const projects = await ctx.db.query.syndicationProject.findMany({
columns: { id: true },
with: {
entity: {
columns: { id: true },
with: {
members: {
columns: {
id: true,
memberId: true,
isControlPerson: true,
},
with: {
member: { columns: { id: true } },
},
},
},
},
},
});