Many-to-many relational query issues

Question

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 albumversionid from photocardsettoalbumversion where photocardsetid = ???.as('albumversionids')
        }
      },
    },
  })
  .prepare()

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

Many-to-many relational query issues

Looking for more? Join the community!