PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – TypeORM sort users by their last sent message timestamp

I’m working on a NestJS application with TypeORM and PostgreSQL, and I need to fetch a list of users sorted by the timestamp of the last message they sent.

I have two entities, User and Message, defined as follows:

user.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Message } from './message.entity';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Message, (message) => message.user)
  messages: Message[];
}

message.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, CreateDateColumn } from 'typeorm';
import { User } from './user.entity';

@Entity('messages')
export class Message {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  message: string;

  @CreateDateColumn({ type: 'timestamp' })
  created: Date;

  @ManyToOne(() => User, (user) => user.messages)
  user: User;
}

Here are some sample records in both tables:

Users Table:

+----+--------+
| id | name   |
+----+--------+
|  1 | John   |
|  2 | Mike   |
|  3 | Norman |
+----+--------+

Messages Table:

+----+--------+-------------+---------------------+
| id | userId | message     | created             |
+----+--------+-------------+---------------------+
|  1 |      3 | Hello there | 2024-08-21 05:30:00 |
|  2 |      2 | Hi!         | 2024-08-22 06:40:00 |
|  3 |      3 | Hello again | 2024-08-23 07:50:00 |
+----+--------+-------------+---------------------+

I want to write a query that fetches all users and sorts them based on the timestamp of their last message. The expected result should be something like this:

[
  {
    "id": 3,
    "name": "Norman"
  },
  {
    "id": 2,
    "name": "Mike"
  },
  {
    "id": 1,
    "name": "John"
  }
]

What I tried so far is:

this.userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.messages', 'message')
  .groupBy('user.id')
  .addGroupBy('user.name')
  .orderBy('MAX(message.created)', 'DESC')
  .getMany();

But I get this error:

"MAX(message" alias was not found. Maybe you forgot to join it?
Tags:

2

Answers


  1. 0

    What you want is a .distinctOn: demo at db-fiddle

    this.userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.messages', 'message')
  .distinctOn('user.id')
  .orderBy({"user.id": "ASC",
            "message.created": "DESC"})
  .getMany();

    Which should roughly translate to this:

    select distinct on("user".id) "user".*, message.message
from "user"
left join message 
on "user".id=message.user_id
order by "user".id asc, 
         message.created desc;

    From PostgreSQL doc:

    SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY.

    In raw SQL you can order by aggregates directly but in your case leftJoinAndSelect would leave a dangling message field you neither grouped by nor used in an aggregate function, so even if injecting the MAX(message.created) worked, you’d get another error complaining about that message field: demo2 at db-fiddle

    select "user".*,message.message
from "user"
left join message 
on "user".id=message.user_id
group by "user".id
order by max(message.created) desc;

    error: column "message.message" must appear in the GROUP BY clause or be used in an aggregate function
    Login or Signup to reply.
  2. 0

    Your simple query

    What you were thinking of is basically valid in standard SQL (incl. Postgres):

    SELECT u.id, u.name  -- ①
FROM   users u
LEFT   JOIN messages m ON m."userId" = u.id
GROUP  BY u.id
ORDER  BY max(m.created) DESC;

    ① Do not list any columns from table messages, you don’t want to display them.

    The manual about expressions in the ORDER BY list:

    Each expression can be the name or ordinal number of an output column
    (SELECT list item), or it can be an arbitrary expression formed from
    input-column values.

    But your ORM seems to expect names only.
    The widespread willingness to put up with half-baked ORMs instead of using universally applicable, unrestricted SQL is remarkable.

    Plus, the sort order is not deterministic, yet. See explanation below. You rather want:

    SELECT u.id, u.name
FROM   users u
LEFT   JOIN messages m ON m."userId" = u.id
GROUP  BY u.id
ORDER  BY max(m.created) DESC NULLS LAST, u.name, u.id;

    View on DB Fiddle

    Better query

    Depending on undisclosed details of your setup, other query styles are (much) faster.

    DISTINCT ON (like Zegarek provided) is nice and simple – and fast for only few messages per user.

    But ORDER BY cannot disagree with DISTINCT ON, so you’d end up with users sorted by id first (not what you want) and need a second query level to sort after distilling the latest created per user. See:

    What’s more, DISTINCT ON only enters the picture to fix a situation created by the join. While fetching all users, it’s (much) faster and simpler to aggregate before joining:

    SELECT u.id, u.name
FROM   users u
LEFT   JOIN (
   SELECT "userId", max(created) AS last_msg
   FROM   messages
   GROUP  BY "userId"  -- ①
   ) m ON m."userId" = u.id
ORDER  BY m.last_msg DESC NULLS LAST  -- ②
        , u.name, u.id;  -- ③

    View on DB Fiddle

    ① We might use DISTINCT ON here, but a plain aggregate is just as well in the simple subquery.

    ② Users with no messages end up with null for last_msg, which sorts first in descending order. You’d get users without messages first, which is certainly not what you want. Add NULLS LAST. See:

    ③ Without tiebreaker(s), users with the same last_msg are sorted arbitrarily. (Sort order may change between calls.)

    If there are many messages per user, emulate an index skip scan:

    If you only want users with messages, a different query is slightly better.

    If you only want the top N users, a different query is (much) better.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search