TypeORM distinctOn with orderBy

user_18842132 0
Viewed 88

I'm trying to get the latest message for a user, either if he is the sender or the receiver.

But I can't use distinctOn with orderBy

const query = this.createQueryBuilder('message')
                  .orderBy('message.date', 'DESC')
                  .where('message.senderId = :id OR message.receiverId = :id',{id:user.id})
                  .distinctOn(['message.senderId','message.receiverId'])

const messages = await query.getRawMany();

SELECT DISTINCT ON ("message"."senderId", "message"."receiverId") "message"."id" AS "message_id", "message"."message" AS "message_message", "message"."date" AS "message_date", "message"."status" AS "message_status", "message"."senderId" AS "message_senderId", "message"."receiverId" AS "message_receiverId" 
FROM "message" "message" 
WHERE "message"."senderId" = $1 
   OR "message"."receiverId" = $2 
ORDER BY "message"."date" DESC

SELECT DISTINCT ON expressions must match initial ORDER BY expressions

typeorm nestjs express node.js sql
1 Answers

You can also try this way

const messages = this.createQueryBuilder('message')
        .distinctOn('DISTINCT ON (message.senderId) as senderId, message.receiverId' as "receiverId")
        .where('message.senderId = :id OR message.receiverId = :id',{id:user.id})
        .orderBy('message.date', 'DESC').getRawMany()
user_39284040
Related