[FEATURE]: "through" relation queries

[kidqueb]

Describe want to want

I think having the ability to jump through relations without additional depth would be handy.

Given the following schema:

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
  }
);

export const groups = pgTable('groups', {
    id: serial('id').primaryKey(),
  }
);

export const groupMemberships = pgTable('group_memberships', {
    userId: integer('user_id').references(() => users.id),
    groupId: integer('group_id').references(() => groups.id),
  }
);

This is an over simplified example, but it shows the desired result.

const { groupMemberships, ...user } = await db.query.users.findFirst({
  with: {
    groupMemberships: {
      limit: 10,
      with: {
        group: true
      }
    }
  }
})

const userWithJustGroups = { 
  ...user, 
  groups: groupMemberships.map(groupMembership => groupMembership.group)
} 

So that we can jump through relationships and have them returned in the results itself. I offered two possible api designs but not sure which would be more effective.

Define "through" in relations and query using with

export const userRelations = relations(users, ({ many, through }) => ({
  groupMemberships: many(groupMemberships),
  // option 1
  groups: many(groups, { through: groupMemberships })),
  // option 2
  groups: through(many(groupMemberships), many(groups)),
}))

const { groups, ...user } = await db.query.users.findFirst({
  with: {
    groups: {
      limit: 10
    }
  }
})

No relation definition, handled in fn args

const { groups, ...user } = await db.query.users.findFirst({
  through: {
    groupMemberships: {
      groups: { limit: 10 }
    }
  }
})

Prior Art

• https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association
• https://laravel.com/docs/10.x/eloquent-relationships#has-many-through
• https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3 :through

[kylekz]

Would love this too. I initially scrolled past this request based on the title, since your example (and how I'm using many-to-many) is how I actually expected many-to-many relations to work, and not how I remembered many-through relations working in Laravel.

In Laravel/Eloquent, $user->groups returns an array of Group models, not UserToGroup models. It doesn't make you manually jump through the pivot table like Drizzle does, it does that for you.

I actually thought the second argument to many() was for this purpose, since it accepts { relationName: string }, but it turns out that's also on one() and doesn't work. :(

[andersoncardoso]

This would be huge. Tbh this is one of the main issues holding us from migrating a project from Prisma to Drizzle, together with full text search.

[statusunknown418]

any updates here?

[neeeeecka]

I am finding it very lacking. Why is this still not supported by december of 2023?