DT
Drizzle Team•2d ago
jkb

Order by on Joined Tables

I am attempting to do an order by for a joined table 
export const playerItem = pgTable(
  "playerItem",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    playerId: uuid().references(() => player.id),
    ...defaultColumns,
  },
  (t) => [index("player_item_pk_index").on(t.id)],
)
export const player = pgTable(
  "player",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    fullName: text().notNull(),
    firstName: text().notNull(),
    lastName: text().notNull(),

    ...defaultColumns,
  },
  (t) => [index("player_pk_index").on(t.id)],
)
export const playerItem = pgTable(
  "playerItem",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    playerId: uuid().references(() => player.id),
    ...defaultColumns,
  },
  (t) => [index("player_item_pk_index").on(t.id)],
)
export const player = pgTable(
  "player",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    fullName: text().notNull(),
    firstName: text().notNull(),
    lastName: text().notNull(),

    ...defaultColumns,
  },
  (t) => [index("player_pk_index").on(t.id)],
)
I want to query playerItem with an order by on player.lastName descending. I don't see any examples in the documentation on how to do this or if Drizzle allows it. This is a simplified example of the schema, denormalizing would make sense here, but I purposely excluded the rest of the columns/relations to make reading easier Any help would be appreciated!
14 Replies
TOSL
TOSL•2d ago
https://orm.drizzle.team/docs/rqb#order-by
Drizzle ORM - Query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
jkb
jkbOP•2d ago
This will only order by the player table not the return of the playerItem Essentially looking to order playerItem based on player.lastName
TOSL
TOSL•2d ago
Have you written anything? Show me the query you're using right now?
jkb
jkbOP•2d ago
Im tyring to get a more basic example working. Im not using the tables mentioned above because my local db is not filled with that data yet. My users table is 
export const user = pgTable(
  "users",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    firstName: text(),
    lastName: text(),
    lastLogin: timestamp({ precision: 6, withTimezone: true }),
    ...defaultColumns,
  },
  (t) => [index("user_pk_index").on(t.id)],
)
export const userRoleMappings = pgTable(
  "userRoleMappings",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    userId: uuid().references(() => user.id),
    userRoleId: integer()
      .notNull()
      .references(() => userRoles.id),
    ...defaultColumns,
  },
  (t) => [index("user_role_mapping_pk_index").on(t.id)],
)

db.query.users.findMany({
  with:{
    userRoleMappings:true
  },
  orderBy:(userRoleMappings, { asc }) => [asc(userRoleMappings.userRoleId)]
})
export const user = pgTable(
  "users",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    firstName: text(),
    lastName: text(),
    lastLogin: timestamp({ precision: 6, withTimezone: true }),
    ...defaultColumns,
  },
  (t) => [index("user_pk_index").on(t.id)],
)
export const userRoleMappings = pgTable(
  "userRoleMappings",
  {
    id: uuid()
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    userId: uuid().references(() => user.id),
    userRoleId: integer()
      .notNull()
      .references(() => userRoles.id),
    ...defaultColumns,
  },
  (t) => [index("user_role_mapping_pk_index").on(t.id)],
)

db.query.users.findMany({
  with:{
    userRoleMappings:true
  },
  orderBy:(userRoleMappings, { asc }) => [asc(userRoleMappings.userRoleId)]
})
In the example above I want to return users ordered by their userRoleId this would be very similar to the other tables above, but I want to order the parent by a relational table @TOSL { "status": "error", "error": "syntax error at or near "desc"" } That query throws this error ^
TOSL
TOSL•2d ago
Move the orderby inside the with Or have you tried that and it isn't the output you're looking for?
jkb
jkbOP•2d ago
I did try that and it only sorts the userRoleMappings not the users
TOSL
TOSL•2d ago
I see.
jkb
jkbOP•2d ago
db.query.users.findMany({
  with: {
    userRoleMappings: {
      orderBy: (userRoleMappings, { desc }) => [
        desc(userRoleMappings.userRoleId),
      ],
    },
  },
});
db.query.users.findMany({
  with: {
    userRoleMappings: {
      orderBy: (userRoleMappings, { desc }) => [
        desc(userRoleMappings.userRoleId),
      ],
    },
  },
});
TOSL
TOSL•2d ago
Query can't order by related fields at least not directly you can use a subquery or just use db.select()
jkb
jkbOP•2d ago
Thats what I did get working here: db.select().from(users).innerJoin(userRoleMappings,eq(users.id,userRoleMappings.userId)).orderBy(asc(userRoleMappings.userRoleId)).limit(5).execute()
TOSL
TOSL•2d ago
Yeah this your best bet. you shouldn't need execute() though
jkb
jkbOP•2d ago
Im suprised they don't allow for the ordering by relation figured it would be a common use case :Shrug:
TOSL
TOSL•2d ago
It's just not supported. The RQB using json aggregation to return the tables as arrays. It's just a limitation of how it was built. I believe it something they intend to improve in relation api v2
jkb
jkbOP•2d ago
@TOSL thanks for the help, looking forward to being able to use this in the future

Did you find this page helpful?