2 replies

Many-to-many relational query issues

schema, relations, statements: https://gist.github.com/kylewardnz/37104f989807e96555ea856294a2b670

1) executing the

fetchArtistWithContent

fetchArtistWithContent

statement returns only the pivot table data on the

members

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

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

{ relationName: 'member' }

as a second argument to

many()

many()

(and vice versa on the inverse relation) results in

There is not enough information to infer relation "artists.members"

There is not enough information to infer relation "artists.members"

so I'm guessing this isn't the use case

2) executing the

fetchAlbumWithContent

fetchAlbumWithContent

statement results in a couple things:
- the above issue on the

versions.photocardSets

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

albumVersionIds

onto each record under the

photocardSets

photocardSets

key, which is just a list of

albumVersion

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)

Many-to-many relational query issues

schema, relations, statements: https://gist.github.com/kylewardnz/37104f989807e96555ea856294a2b670

1) executing the

fetchArtistWithContent

fetchArtistWithContent

statement returns only the pivot table data on the

members

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

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

{ relationName: 'member' }

as a second argument to

many()

many()

(and vice versa on the inverse relation) results in

There is not enough information to infer relation "artists.members"

There is not enough information to infer relation "artists.members"

so I'm guessing this isn't the use case

2) executing the

fetchAlbumWithContent

fetchAlbumWithContent

statement results in a couple things:
- the above issue on the

versions.photocardSets

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

albumVersionIds

onto each record under the

photocardSets

photocardSets

key, which is just a list of

albumVersion

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)

Many-to-many relational query issues

Many-to-many relational query issues

Similar Threads

Similar Threads

Similar Threads