[FEATURE]: Support for ordering/filtering on relations in drizzle `findMany` query method

[marceloverdijk]

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 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.

[McKean]

Referencing #1169 and #696 as this seems like a duplicate.