Drizzle ORM: Dynamic "orderBy" on Joined Tables

Question

I have an app where I have to sort the users data based on it's role name. Below is the schema for both users and roles tables:

/** 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],
   }),
}));

Here is how I query the users data:

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: () => {
          /** Doesn't work for it is ambiguous which table to reference from. */
          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);

How will I be able to create a dynamic orderBy statement/callback that can sort by either a column from the users table as well as from the roles table? And if possible, is there a way to get the same result without resorting to joining tables, instead, by using relationship queries specifically?