[BUG]: ordering on related field tries to order on the root table

[OmegaDroid]

What version of drizzle-orm are you using?

0.29.1

What version of drizzle-kit are you using?

0.20.7

Describe the Bug

When ordering by a related field on a joined table using the query syntax the generated sql tries to order on the root table rather than the joined table.

I have a groupMembership object that has a related user (many to one):

export const users = pgTable("auth_users", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("emailVerified").defaultNow(),
  image: varchar("image", { length: 255 }),
  role: userRoleEnum("role").default("MEMBER").notNull(),
});

export const userRelations = relations(users, ({ many }) => ({
    groupMemberships: many(groupMemberships),
}));

export const groupMemberships = pgTable("groups_groupMemberships", {
    id: varchar("id", { length: 25 }).notNull().primaryKey(),
    createdAt: timestamp("createdAt").notNull().defaultNow(),
    modifiedAt: timestamp("modifiedAt").notNull().defaultNow(),
    groupId: varchar("groupId", { length: 25 }).notNull().references(() => groups.id),
    userId: varchar("userId", { length: 255 }).notNull().references(() => users.id),
    role: groupRoleEnum("role").default("NEW").notNull(),
})

export const groupMembershipRelatons = relations(groupMemberships, ({ one }) => ({
    group: one(groups, {
        fields: [groupMemberships.groupId],
        references: [groups.id],
    }),
    user: one(users, {
        fields: [groupMemberships.userId],
        references: [users.id],
    }),
}))

When creating a query with the select syntax I get the correct ordering:

const q1 = db.select().from(schema.groupMemberships)
            .leftJoin(schema.users, eq(schema.users.id, schema.groupMemberships.id))
            .orderBy(schema.users.name)

console.log(q1.toSQL().sql)
'''
select "groups_groupMemberships"."id", "groups_groupMemberships"."createdAt", 
"groups_groupMemberships"."modifiedAt", "groups_groupMemberships"."groupId", "groups_groupMemberships"."userId", 
"groups_groupMemberships"."role", "auth_users"."id", "auth_users"."name", "auth_users"."email", 
"auth_users"."emailVerified", "auth_users"."image", "auth_users"."role" from "groups_groupMemberships" left join 
"auth_users" on "auth_users"."id" = "groups_groupMemberships"."id" order by "auth_users"."name"
'''

using the query syntax

const q2 = db.query.groupMemberships.findMany({
    with: {
        user: true
    },
    orderBy: schema.users.name,
})

console.log(q2.toSQL().sql)
'''
select "groupMemberships"."id", "groupMemberships"."createdAt", "groupMemberships"."modifiedAt", 
"groupMemberships"."groupId", "groupMemberships"."userId", "groupMemberships"."role", 
"groupMemberships_user"."data" as "user" from "groups_groupMemberships" "groupMemberships" left join lateral (select 
json_build_array("groupMemberships_user"."id", "groupMemberships_user"."name", "groupMemberships_user"."email", 
"groupMemberships_user"."emailVerified", "groupMemberships_user"."image", "groupMemberships_user"."role") as "data" 
from (select * from "auth_users" "groupMemberships_user" where "groupMemberships_user"."id" = 
"groupMemberships"."userId" limit $1) "groupMemberships_user") "groupMemberships_user" on true order by 
"groupMemberships"."name"
'''

If I resolve the later query I get an error column groupMemberships.name does not exist.

Expected behavior

I would expect the query syntax to order on the joined table as with the select syntax.

Environment & setup

Running against postgres with drizzle-orm/node-postgres

[Angelelz]

Right now, ordering, filtering or referring to an inner related table or column is not supported. The RQB uses json aggregation to return the related tables as arrays as you can see in the raw query. I mean, technically, it's just a json column and you can probably make it work by using json functions and knowing the aliases in the orderBy.

[OmegaDroid]

Thanks @Angelelz, for now I can continue using the select but having this supported by query would be very handy.

[zmr-dev]

I had the same scenario and had to replace all my findMany with selects =(

[seivad]

Any update or idea when this will get fixed? If you look at the docs, it says it would work but I have the same issue. Games with gameConfigs, gameConfigs have a column for ordering by (priority) and it just breaks no matter what way you try to orderBy.

Column parentTable.priority does not exist when appending an OrderBy at the top level.

map error when orderBy is within the with: { gameConfig: {} } section.