Drizzle Team•16mo ago

Utilizing Many-to-Many relationships

Yo, I'm trying to make sense of a the drizzle way to handling many-to-many relations, but can't seem to understand how to get the related object with one statement (like in a classing double-join query) could somebody give me an example of a query utilizing this relationship? for example - when setting up the example here: https://orm.drizzle.team/docs/rqb#many-to-many

Relational queries – DrizzleORM

Drizzle ORM | %s

2 Replies

Noahh•16mo ago

Sure! There isn't exactly a way to directly get a many-to-many object when you're using a join table. A query from that example might look something like

const user = db.query.users.findFirst({
  where: eq(user.id, userId),
  with: {
    usersToGroups: {
      with: {
        group: true
      }
    }
  }
})

const user = db.query.users.findFirst({
  where: eq(user.id, userId),
  with: {
    usersToGroups: {
      with: {
        group: true
      }
    }
  }
})

This would return something along the lines of:

{
  id: 1,
  name: 'BaNuni'
  usersToGroups: [{
    userId: 1,
    groupId: 5,
    group: {
      id: 5,
      name: 'Soccer'
    }
  }, {
    userId: 1,
    groupId: 32,
    group: {
      id: 32,
      name: 'Music'
    }
  }]
}

{
  id: 1,
  name: 'BaNuni'
  usersToGroups: [{
    userId: 1,
    groupId: 5,
    group: {
      id: 5,
      name: 'Soccer'
    }
  }, {
    userId: 1,
    groupId: 32,
    group: {
      id: 32,
      name: 'Music'
    }
  }]
}

Which you could obviously use TypeScript to map to be what I believe you're looking for:

{
  id: 1,
  name: 'BaNuni'
  groups: [{
      id: 5,
      name: 'Soccer'
  }, {
      id: 32,
      name: 'Music'
  }]
}

{
  id: 1,
  name: 'BaNuni'
  groups: [{
      id: 5,
      name: 'Soccer'
  }, {
      id: 32,
      name: 'Music'
  }]
}

Right now, as far as I'm aware, it's not currently possible to "map" the related object, skipping the many-to-many table, without jumping into the SQL

BaNuniOP•16mo ago

Amazing - this is what I was looking for. Maybe we should add a 'doubleWith' operator or 'withConjunction' to make this more readable.

Gaming

Programming

Utilizing Many-to-Many relationships