DT
Drizzle Team•2y ago
Andrey Los

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 Los
Andrey LosOP•2y 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
Noahh•2y 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 Los
Andrey LosOP•2y 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
Noahh•2y ago
that also works yeah! I agree it'd be cool to have it built in
Will
Will•2y 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 Los
Andrey LosOP•2y ago
Nope 🙂 I'm using supertokens.com for auth BTW. So far pretty solid.
Will
Will•2y 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 Los
Andrey LosOP•2y 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 🙂

Did you find this page helpful?