drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: Support for ordering/filtering on relations in drizzle `findMany` query method
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Discussed in https://github.com/drizzle-team/drizzle-orm/discussions/2639
Originally posted by **marceloverdijk** July 15, 2024
From the drizzle docs `orderBy` could be used like:
```
import { desc, asc } from 'drizzle-orm';
await db.query.posts.findMany({
orderBy: [asc(posts.id)],
});
```
but what if I want to order on the authors name in case `authors` is separate table which `posts` has a foreign key to?
Prisma supports this cleanly using:
```
const posts = await prisma.post.findMany({
orderBy: {
author: {
email: 'asc',
},
},
})
```
It would be very useful if Drizzle would support this as well.
Although the Drizzle [docs](https://orm.drizzle.team/docs/rqb) state:
> ### Drizzle Queries
>
> Drizzle ORM is designed to be a thin typed layer on top of SQL. We truly believe we’ve designed the best way to operate an SQL database from TypeScript and it’s time to make it better.
>
> Relational queries are meant to provide you with a great developer experience for querying nested relational data from an SQL database, avoiding multiple joins and complex data mappings.
to implement (this quite simple and common use case) with Drizzle, we have to step away from the `findMany` query method and use the more low level query builder instead:
```
select({
...getTableColumns(posts),
})
.from(posts)
.leftJoin(authors, eq(authors.Id, posts.authorId)
.orderBy(asc(authors.name))
```
This will order on the author name (but this way also filtering on the author could be added), but only return the posts.
Contributor guide
Assessment
This issue has not been assessed yet.