How to order by nested/FK property (posts --> author)

[marceloverdijk]

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?

[Jim-salies]

I'm not sure you can do this using query. The best approach is probably to build your own query using select.

select({
    // getTableColumns is an utils from drizzle
     ...getTableColumns(post),
    author: ***aggregate author data here if needed???***
})
.from(post)
.innerJoin(author, eq(author.uuid, post.authorUuid)
.orderBy(desc(author.name))

if you need to aggregate author data, you will probably have to implement aggregate function like json_build_object in postgresql

[marceloverdijk]

That's a real pity.
Prisma supports this cleanly using:

const posts = await prisma.post.findMany({
  orderBy: {
    author: {
      email: 'asc',
    },
  },
})

[marceloverdijk]

I've created a feature request for supporting ordering/filtering on relations in findMany query method:
#2650

[nagy135]

these small issues are really frustrating ...i ve build big query functionality for many tables, but now i realized i need to support ordering by these joined relations. So i guess i m going to have to completely rewrite all of it using "almost raw sql". Thats so sad after so deep into project