Drizzle Team•3mo ago

How to query many to many? Are the docs out of date?

I've got two tables... Events and Drinks. Each each event can have multiple drinks, and a drink can be in multple events. So I've setup a many-to-many relation as per the docs. (https://orm.drizzle.team/docs/rqb#many-to-many) schema: https://gist.github.com/magicspon/b52863a5f3b8660665e9f36d327b8cc2 When I try an run this query:

db.query.events.findMany({
  with: {
    eventsToDrink: {
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});

db.query.events.findMany({
  with: {
    eventsToDrink: {
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});

VS Code says no, "eventsToDrink" doesn't exist on events... Yet it is auto suggested in drizzle studio.. When I run the query in drizzle studio, i get an error

5 Replies

magicspon•3mo ago

I can get the snaps related items (one-to-many) with the with syntax... but many-to-many is a dead duck

MrRazia•3mo ago

See if it helps to add a relation name to the relations. The related relations should have the same name to associate it. eg:

export const eventsRelations = relations(events, ({ many }) => ({
    drinks: many(eventsToDrinks, relationName: 'eventToDrinks'),
}))

export const eventsRelations = relations(events, ({ many }) => ({
    drinks: many(eventsToDrinks, relationName: 'eventToDrinks'),
}))

Sillvva•3mo ago

You should change the relation key

export const eventsRelations = relations(events, ({ many }) => ({
-    drinks: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

export const drinksRelations = relations(drinks, ({ many, one }) => ({
-    events: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

export const eventsRelations = relations(events, ({ many }) => ({
-    drinks: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

export const drinksRelations = relations(drinks, ({ many, one }) => ({
-    events: many(eventsToDrinks),
+    eventsToDrinks: many(eventsToDrinks),
}))

The key used in the rqb object should match the relation key

db.query.events.findMany({
  with: {
    eventsToDrink: { // this key should match the eventsRelations key, which it does not
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true, // this key should match the eventsToDrinkRelations key, which it already does
      },
    },
  },
});

db.query.events.findMany({
  with: {
    eventsToDrink: { // this key should match the eventsRelations key, which it does not
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true, // this key should match the eventsToDrinkRelations key, which it already does
      },
    },
  },
});

Alternatively

db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});

db.query.events.findMany({
  with: {
    drinks: { // you could also change this key
      columns: {
        drinkId: true,
        eventId: true,
      },
      with: {
        drinks: true,
      },
    },
  },
});

rphlmr ⚡•3mo ago

You can try @Sillvva example here: https://drizzle.run/ark1boqh4pq1904m4alcyyam

Drizzle Run

Many to many with query API - Drizzle Run

magicspon•3mo ago

Amazing... thanks @MrRazia @Sillvva @Raphaël M (@rphlmr) ⚡

Gaming

Programming

How to query many to many? Are the docs out of date?