Drizzle TeamDT
Drizzle Team•3y ago
Kairu

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,
  },
},

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),
}))

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()


(figured I'd make another thread since this is unrelated to kit)
Was this page helpful?