Drizzle Team•16mo ago

There is not enough information to infer relation. What am I doing wrong?

I have such a setup yet I'm getting There is not enough information to infer relation "users.organizations". What am I missing? (below are all my relations) It's a many-to-many relationship. I also registered all that stuff into the schema (below)

await db.query.users.findFirst({
      where: eq(users.id, user.id),
      with: { organizations: true },
    })

await db.query.users.findFirst({
      where: eq(users.id, user.id),
      with: { organizations: true },
    })

import { relations } from "drizzle-orm"
import { pgTable, primaryKey, uuid } from "drizzle-orm/pg-core"

import { organizations } from "./organizations"
import { users } from "./user"

export const usersToOrganizations = pgTable(
  "usersToOrganizations",
  {
    userId: uuid("userId")
      .notNull()
      .references(() => users.id),
    organizationId: uuid("organizationId")
      .notNull()
      .references(() => organizations.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.organizationId),
  }),
)

export const usersToOrganizationsRelations = relations(
  usersToOrganizations,
  ({ one }) => ({
    organization: one(organizations, {
      fields: [usersToOrganizations.organizationId],
      references: [organizations.id],
    }),
    user: one(users, {
      fields: [usersToOrganizations.userId],
      references: [users.id],
    }),
  }),
)

export const usersRelations = relations(users, ({ many }) => ({
  organizations: many(organizations),
}))

export const organizationsRelations = relations(organizations, ({ many }) => ({
  users: many(users),
}))

import { relations } from "drizzle-orm"
import { pgTable, primaryKey, uuid } from "drizzle-orm/pg-core"

import { organizations } from "./organizations"
import { users } from "./user"

export const usersToOrganizations = pgTable(
  "usersToOrganizations",
  {
    userId: uuid("userId")
      .notNull()
      .references(() => users.id),
    organizationId: uuid("organizationId")
      .notNull()
      .references(() => organizations.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.organizationId),
  }),
)

export const usersToOrganizationsRelations = relations(
  usersToOrganizations,
  ({ one }) => ({
    organization: one(organizations, {
      fields: [usersToOrganizations.organizationId],
      references: [organizations.id],
    }),
    user: one(users, {
      fields: [usersToOrganizations.userId],
      references: [users.id],
    }),
  }),
)

export const usersRelations = relations(users, ({ many }) => ({
  organizations: many(organizations),
}))

export const organizationsRelations = relations(organizations, ({ many }) => ({
  users: many(users),
}))

export const db = drizzle(queryClient, {
  logger: new DrizzleLogger(),
  schema: {
    users,
    organizations,
    identities,
    usersToOrganizations,
    identitiesRelations,
    organizationsRelations,
    usersToOrganizationsRelations,
    usersRelations,
  },
})

export const db = drizzle(queryClient, {
  logger: new DrizzleLogger(),
  schema: {
    users,
    organizations,
    identities,
    usersToOrganizations,
    identitiesRelations,
    organizationsRelations,
    usersToOrganizationsRelations,
    usersRelations,
  },
})

8 Replies

Andrey LosOP•16mo ago

Also, unsure about is goodie 🙂 https://orm.drizzle.team/docs/goodies#compare-objects-types-instanceof-alternative Can you show better example?

Goodies – DrizzleORM

Drizzle ORM | %s

Noahh•16mo ago

It looks like that's because your usersRelations are relating directly to organizations and vice versa instead of using usersToOrganizations` It should probably look something more like this:

import { relations } from "drizzle-orm"
import { pgTable, primaryKey, uuid } from "drizzle-orm/pg-core"

import { organizations } from "./organizations"
import { users } from "./user"

export const usersToOrganizations = pgTable(
  "usersToOrganizations",
  {
    userId: uuid("userId")
      .notNull()
      .references(() => users.id),
    organizationId: uuid("organizationId")
      .notNull()
      .references(() => organizations.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.organizationId),
  }),
)

export const usersToOrganizationsRelations = relations(
  usersToOrganizations,
  ({ one }) => ({
    organization: one(organizations, {
      fields: [usersToOrganizations.organizationId],
      references: [organizations.id],
    }),
    user: one(users, {
      fields: [usersToOrganizations.userId],
      references: [users.id],
    }),
  }),
)

export const usersRelations = relations(users, ({ many }) => ({
  organizations: many(usersToOrganizations), // Change this!
}))

export const organizationsRelations = relations(organizations, ({ many }) => ({
  users: many(usersToOrganizations), // Change this!
}))

import { relations } from "drizzle-orm"
import { pgTable, primaryKey, uuid } from "drizzle-orm/pg-core"

import { organizations } from "./organizations"
import { users } from "./user"

export const usersToOrganizations = pgTable(
  "usersToOrganizations",
  {
    userId: uuid("userId")
      .notNull()
      .references(() => users.id),
    organizationId: uuid("organizationId")
      .notNull()
      .references(() => organizations.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.organizationId),
  }),
)

export const usersToOrganizationsRelations = relations(
  usersToOrganizations,
  ({ one }) => ({
    organization: one(organizations, {
      fields: [usersToOrganizations.organizationId],
      references: [organizations.id],
    }),
    user: one(users, {
      fields: [usersToOrganizations.userId],
      references: [users.id],
    }),
  }),
)

export const usersRelations = relations(users, ({ many }) => ({
  organizations: many(usersToOrganizations), // Change this!
}))

export const organizationsRelations = relations(organizations, ({ many }) => ({
  users: many(usersToOrganizations), // Change this!
}))

then to query it you'll have to nest it like:

db.query.users.findFirst({
  with: {
    organizations: {
      with: {
        organization: true
      }
    }
  }
})

db.query.users.findFirst({
  with: {
    organizations: {
      with: {
        organization: true
      }
    }
  }
})

and you'll get:

{
  ...user,
  organizations: [{
    userId: string,
    organizationId: string,
    organization: {
      ...organization
    }
  }]
}

{
  ...user,
  organizations: [{
    userId: string,
    organizationId: string,
    organization: {
      ...organization
    }
  }]
}

Andrey LosOP•16mo ago

It's a bit ugly 🙂 Would be awesome if it would be possible to have these many-to-many resolves more "magically" 🙂 I end up doing the changes that you mentioned and having a query like that.

await db.query.usersToOrganizations.findFirst({
      where: eq(usersToOrganizations.userId, user.id),
    })

await db.query.usersToOrganizations.findFirst({
      where: eq(usersToOrganizations.userId, user.id),
    })

Noahh•16mo ago

that also works yeah! I agree it'd be cool to have it built in

Will•16mo ago

@rip21 so glad you posted this, I'm dealing with the same exact thing right now. Any chance you're also using next-auth and trying to figure out how to redirect to an organization id dynamic URL path after signin?

Andrey LosOP•16mo ago

Nope 🙂 I'm using supertokens.com for auth BTW. So far pretty solid.

Will•16mo ago

man i just dont want to pay for auth lol but maybe i should so i dont have to deal with headaches like this!!

Andrey LosOP•16mo ago

If you have 5k MAU then it's a good problem to have 🙂 Beacuse it's free for that amount. If you have 100k etc so it costs a lot, then, well, again, you can always self-host making it cheaper 🙂

Gaming

Programming

There is not enough information to infer relation. What am I doing wrong?