The issue you’re facing with the query is that the alias message is not properly recognized in the MAX(message.created) expression. In TypeORM, you need to ensure that the join and the usage of the alias in the MAX function are correctly structured.

Here’s a corrected approach to achieve the desired result:

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

Key Points:

You are using leftJoin instead of leftJoinAndSelect because you don’t need to select all the messages. You just need the created timestamp for sorting.
The groupBy and orderBy are correctly used for aggregating by user and sorting by the maximum message.created.
If you also want to avoid joining messages if no messages exist for a user, consider filtering those users by using a HAVING clause. However, the above approach should meet your requirements for fetching users sorted by their last message timestamp.

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

Your simple query

What you were thinking of is basically valid in Postgres – and standard SQL:1999 (id being the primary key):

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. (As in the outdated SQL:92 standard.) See:

• max(), group by and order by

_{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:

• Select first row in each GROUP BY group?
• PostgreSQL DISTINCT ON with different ORDER BY

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:

• Sort by column ASC, but NULL values first?

③ 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:

• SELECT DISTINCT is slower than expected on my table in PostgreSQL
• Optimize GROUP BY query to retrieve latest row per user

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.

Your query will be this:

SELECT u.id, u.name
FROM users u
LEFT JOIN messages m ON m.user_id = u.id
GROUP BY u.id
ORDER BY max(m.created_at) DESC;

In typeORM it will be:

this.userRepository
  .createQueryBuilder('user')
  .select('user.id')
  .addSelect('user.name')
  .leftJoin(Message, 'msg', 'msg."userId" = user.id') 
  .groupBy('user.id')
  .orderBy('MAX(msg.created)', 'DESC')
  .getMany();

make sure that the alias for the aggregated column is correctly referenced in both the addSelect and orderBy methods. Moreover, you should ensure that the join is correctly set up and that the query builder is aware of the message alias

import { getRepository } from 'typeorm';
import { User } from './User';
import { Message } from './Message';

async function getUsersSortedByLastMessageTimestamp() {
  const userRepository = getRepository(User);

  const users = await userRepository
    .createQueryBuilder('user')
    .leftJoin('user.messages', 'message')
    .addSelect('MAX(message.timestamp)', 'lastMessageTimestamp')
    .groupBy('user.id')
    .orderBy('lastMessageTimestamp', 'DESC')
    .getMany();

  return users;
}