Django: Filtering Distinct Data

I'm trying to build a messaging app. Here's my model, class Message(models.Model): sender = models.ForeignKey(User, related_name='sender') receiver = models.ForeignKey(User, relate

Solution 1:

You could query the senders and receivers separately and then use set() to get the distinct values

users = set([x.receiver for x in Message.objects.filter(sender = request.user)] + [x.sender for x in Message.objects.filter(receiver = request.user)]) 

Solution 2:

With your db structure i was able to receive what your want only with such db query (not sure it can be reproduced in django orm):

SELECT u.*, m.*
FROM auth_user u
  INNER JOIN (
     SELECT
       user_id,
       MAX(message_id) AS message_id
     FROM
       (
         SELECT
           m2.receiver_id AS user_id,
           MAX(id)        AS message_id
         FROM yourapp_message m2
         GROUP BY m2.receiver_id
         UNION
         SELECT
           m2.sender_id AS user_id,
           MAX(id)      AS message_id
         FROM yourapp_message m2
         GROUP BY m2.sender_id
       ) t
     GROUP BY user_id) t2 ON u.id = t2.user_id
  INNER JOIN yourapp_message m ON m.id = t2.message_id
ORDER BY m.id DESC