I'm Trying to get the latest message for a user, Ether if he is the sender or the receiver But I can't use distinctOn with orderBy
const quiry = 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 quiry.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
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()