Drizzle ORM：关联表动态orderBy实现及关联查询替代方案

我开发了一个应用，需要根据角色名称对用户数据进行排序。以下是users表和roles表的Schema：

/** Roles Table */
export const roles = pgTable("roles", {
   id: bigserial("id", { mode: "number" }).primaryKey(),
   name: varchar("name", { length: 255 }).unique().notNull(),
   createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
   updatedAt: timestamp("updated_at", { mode: "date" }).defaultNow(),
});

export const rolesRelations = relations(roles, ({ many }) => ({
   users: many(users),
}));

/** Users Table */
export const users = pgTable("users", {
   id: bigserial("id", { mode: "number" }).primaryKey(),
   firstName: varchar("first_name", { length: 255 }).notNull(),
   lastName: varchar("last_name", { length: 255 }).notNull(),
   username: varchar("username", { length: 15 }).unique().notNull(),
   emailAddress: varchar("email_address", { length: 255 }).unique().notNull(),
   password: text("password").notNull(),
   isVerified: boolean("is_verified").default(false),
   createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
   updatedAt: timestamp("updated_at", { mode: "date" }).defaultNow(),

   roleId: bigint("role_id", { mode: "number" })
      .notNull()
      .references(() => roles.id, { onDelete: "restrict", onUpdate: "cascade" }),
});

export const usersRelations = relations(users, ({ one }) => ({
   role: one(roles, {
      fields: [users.roleId],
      references: [roles.id],
   }),
}));

当前的用户数据查询逻辑如下：

const _users = await db
      .select()
      .from(users)
      .innerJoin(roles, eq(users.roleId, roles.id))
      .where(
         and(
            validated.search
               ? or(
                    ilike(users.firstName, `%${validated.search}%`),
                    ilike(users.lastName, `%${validated.search}%`),
                    sql`concat(${users.firstName}, ' ', ${users.lastName}) ilike '%${sql.raw(validated.search)}%'`,
                    ilike(users.emailAddress, `%${validated.search}%`),
                    ilike(users.username, `%${validated.search}%`)
                 )
               : undefined
         )
      )
      .orderBy(() => {
          /** 因无法确定字段所属表而失效 */
          const [field, order] = validated.sortBy.split("-");

          return order === "asc" ? asc(sql.identifier(field)) : desc(sql.identifier(field));
       })
      .offset((validated.page - 1) * validated.perPage)
      .limit(validated.perPage);

需要解决两个问题：

1. 如何实现可同时对用户表或角色表字段进行排序的动态orderBy语句/回调？
2. 是否可以不使用联表查询，改用关联查询达成同样的排序效果？

一、修复联表查询的动态排序问题 #

核心问题是未指定排序字段所属的表，导致数据库无法识别。可以通过提前映射字段与对应表的关系解决：

1. 定义字段映射对象，将前端传入的排序字段关联到数据库表的具体字段：

// 前端传参示例："firstName-asc"、"roleName-desc"
const sortFieldMap = {
  // 用户表字段
  firstName: users.firstName,
  lastName: users.lastName,
  username: users.username,
  emailAddress: users.emailAddress,
  createdAt: users.createdAt,
  // 角色表字段（用别名避免和用户表字段重名）
  roleName: roles.name,
  roleCreatedAt: roles.createdAt
};

2. 修改orderBy逻辑，通过映射表获取明确的表字段：

.orderBy(() => {
  const [field, order] = validated.sortBy.split("-");
  const targetField = sortFieldMap[field];
  
  // 传入无效字段时使用默认排序
  if (!targetField) {
    return desc(users.createdAt);
  }
  
  return order === "asc" ? asc(targetField) : desc(targetField);
})

二、改用关联查询实现排序（无需显式JOIN） #

Drizzle ORM支持通过withRelations加载关联数据，同时可以用子查询实现基于关联表字段的排序：

1. 构建排序逻辑，针对关联字段用子查询获取对应值：

const getSortExpression = () => {
  const [field, order] = validated.sortBy.split("-");
  
  switch(field) {
    // 用户表字段直接排序
    case 'firstName':
      return order === 'asc' ? asc(users.firstName) : desc(users.firstName);
    case 'lastName':
      return order === 'asc' ? asc(users.lastName) : desc(users.lastName);
    case 'username':
      return order === 'asc' ? asc(users.username) : desc(users.username);
    // 角色表字段通过子查询获取
    case 'roleName':
      const roleNameSubquery = sql<string>`(SELECT ${roles.name} FROM ${roles} WHERE ${roles.id} = ${users.roleId})`;
      return order === 'asc' ? asc(roleNameSubquery) : desc(roleNameSubquery);
    // 默认排序规则
    default:
      return desc(users.createdAt);
  }
};

2. 修改查询语句，使用withRelations加载关联数据并应用排序：

const _users = await db
  .select()
  .from(users)
  .withRelations({ role: true }) // 自动加载用户关联的角色数据
  .where(
    and(
      validated.search
        ? or(
            ilike(users.firstName, `%${validated.search}%`),
            ilike(users.lastName, `%${validated.search}%`),
            sql`concat(${users.firstName}, ' ', ${users.lastName}) ilike '%${validated.search}%'`,
            ilike(users.emailAddress, `%${validated.search}%`),
            ilike(users.username, `%${validated.search}%`)
          )
        : undefined
    )
  )
  .orderBy(getSortExpression())
  .offset((validated.page - 1) * validated.perPage)
  .limit(validated.perPage);

这种方式返回的结果中，每个用户对象会嵌套role属性，结构更清晰，无需处理JOIN后的字段冗余问题。

补充说明 #

• 必须对前端传入的sortBy参数做校验，避免传入不存在的字段导致SQL错误，比如提前定义允许的字段列表进行比对。
• 联表查询会返回用户表和角色表的所有字段，若需精简返回数据，可在select()中指定需要的字段；关联查询则返回嵌套结构，数据更规整。

内容的提问来源于stack exchange，提问作者alas.code