Drizzle Team•2y ago

Many-to-many relational query issues

schema, relations, statements: https://gist.github.com/kylewardnz/37104f989807e96555ea856294a2b670 1) executing the fetchArtistWithContent statement returns only the pivot table data on the members relation. This does make sense since it's actually a relation to that table, but logically I'm wanting the data from the members table. This can be done by changing the with statement to:

with: {
  albums: true,
  members: {
    member: true,
  },
},

with: {
  albums: true,
  members: {
    member: true,
  },
},

but now it's all nested under another key. Not the end of the world, but it'd be nice if we were able to specify a relation on that pivot to pull from, something like:

export const artistRelations = relations(artists, ({ one, many }) => ({
  members: many(artistsToMembers, membersTable),
}))

export const artistRelations = relations(artists, ({ one, many }) => ({
  members: many(artistsToMembers, membersTable),
}))

Passing in { relationName: 'member' } as a second argument to many() (and vice versa on the inverse relation) results in There is not enough information to infer relation "artists.members" so I'm guessing this isn't the use case 2) executing the fetchAlbumWithContent statement results in a couple things: - the above issue on the versions.photocardSets key - this issue on all relations: https://github.com/drizzle-team/drizzle-orm/issues/599 then, I was wondering if there was a way within one query, to then append a new key called albumVersionIds onto each record under the photocardSets key, which is just a list of albumVersion ids that it's related to. This would be handy for being able to pass the record directly into an update form. Something like this I suppose? Not sure what would replace ??? and I would guess this causes the n+1 problem too

export const fetchAlbumWithContent = db.query.albums
  .findFirst({
    where: (albums, { eq }) => eq(albums.id, placeholder("id")),
    with: {
      ...
      photocardSets: {
        extras: {
          albumVersionIds: sql<number[]>`select album_version_id from photocard_set_to_album_version where photocard_set_id = ???`.as('album_version_ids')
        }
      },
    },
  })
  .prepare()

export const fetchAlbumWithContent = db.query.albums
  .findFirst({
    where: (albums, { eq }) => eq(albums.id, placeholder("id")),
    with: {
      ...
      photocardSets: {
        extras: {
          albumVersionIds: sql<number[]>`select album_version_id from photocard_set_to_album_version where photocard_set_id = ???`.as('album_version_ids')
        }
      },
    },
  })
  .prepare()

(figured I'd make another thread since this is unrelated to kit)

2 Replies

jack3d.brah•2y ago

hi ! any news ?

KairuOP•2y ago

the above issue on the versions.photocardSets key

this and point 1: no comment yet, but just using the pivot table hasn't been too much of an issue, a minor thing really. i've been doing more type inference instead of explicitly setting return types and such which helps

this issue on all relations: https://github.com/drizzle-team/drizzle-orm/issues/599

this has been fixed within drizzle for a while now for my extras question: since drizzle only ever makes one query, just querying for the full relation and using the pivot table record accomplishes what i was looking for

GitHub

[BUG]: multiple relations in with operator returns too many rows ...

What version of drizzle-orm are you using? 0.26.0 What version of drizzle-kit are you using? 0.18/0 Describe the Bug Using the schema below and a query with multiple relations in the with operator ...

Gaming

Programming

Many-to-many relational query issues