Sorting by field in many-to-many drizzle query

Question

I have set up a m2m relation in drizzle and can query it.

const category = await db.query.categoriesTable.findFirst({
    where: (categoriesTable, { eq }) =>
      eq(categoriesTable.id, params.categoryId),
    with: {
      articles: {
        with: {
          article: {
            columns: {
              title: true,
              content: true,
              timestamp: true,
            },
          },
        },
      },
    },
  });

My question is, given the above code how do I sort articles b timestamp? In the "with" for articles I tried to add

orderBy: (fields) => desc(fields.timestamp),

but ts will only allow fields.articleId or fields.categoryId.

What am I missing?

Answer 1

I don't think it's possible to do the sorting how you want in the with clause

Similar issue on their GH https://github.com/drizzle-team/drizzle-orm/discussions/2639

Feature request for what you're asking for and info from the drizzle contributors on what to do: https://github.com/drizzle-team/drizzle-orm/issues/2650

You're better off using the select syntax AKA the core API in this scenario. So instead of this:

const category = await db.query.categoriesTable.findFirst({
    where: (categoriesTable, { eq }) =>
      eq(categoriesTable.id, params.categoryId),
    orderBy: (categoriesTable, { asc }) => [asc(categoriesTable.id)],
    with: {
      articles: {
        orderBy: (articles, { desc }) => [desc(articles.timestamp)],
        with: {
          article: {
            columns: {
              title: true,
              content: true,
              timestamp: true,
            },
          },
        },
      },
    },
  });

Try the select syntax, but you move away from the with syntax. The syntax might be slightly off but you get the idea

db.select().from(categoriesTable) 
.innerJoin(articles,eq(categoriesTable.id,articles.categoryId))
.innerJoin(article, eq(articles.id, article.articlesId))
.orderBy(articles.timestamp)