'TypeORM distinctOn with orderBy

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
sqlnode.jsexpressnestjstypeorm


Solution 1:[1]

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()

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sagar

Related Questions