Search code examples
pythonsqldjangoormunique

Unique text message objects ordered by date query Django ORM

I have a text message model as follows:

    class TextMessage(models.Model):
        from_phone_number = models.CharField(max_length=25)
        to_phone_number = models.CharField(max_length=25)
        date_of_message = models.DateTimeField(auto_now_add=True)
        message_body = models.TextField()

I am trying to return a Django queryset (it MUST be a queryset) where the from_phone_numbers are unique AND the text message is the most recent text message. So for example if I had the text messages:

**id**| **from_phone_num** | **to_phone_num** | **date_of_message**|  **Message body** |  
7;"19991112222";"19996667777";"2019-11-13 15:07:53.726911-07";"dupe message 2";
4;"19993334444";"19996667777";"2019-11-13 13:50:05.921257-07";"dsfsdfsf";  
3;"19992222222";"19995552323";"2019-11-13 13:49:18.503679-07";"TEST123";
5;"19991112222";"19996667777";"2019-11-13 15:07:21.834347-07";"dupe message 1";

the queryset returned would be: 

**id**| **from_phone_num** | **to_phone_num** | **date_of_message**|  **Message body** |  
7;"19991112222";"19996667777";"2019-11-13 15:07:53.726911-07";"dupe message 2";
4;"19993334444";"19996667777";"2019-11-13 13:50:05.921257-07";"dsfsdfsf";  
3;"19992222222";"19995552323";"2019-11-13 13:49:18.503679-07";"TEST123";

This is the query I have already tried:

TextMessage.objects.order_by('date_of_message','from_phone_number').distinct('date_of_message', 'from_phone_number')

but it didn't give me the expected results. Any help would be appreciated!

Solution

  • Try the below query.

     TextMessage.objects.order_by('from_phone_number', '-date_of_message').distinct('from_phone_number')

    The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So by making the column you use in distinct as the first column in the order_by should work.