Unique text message objects ordered by date query Django ORM

Question

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!

What do you mean by "it didn't work"? Did it raise an exception? Did it order improperly? If so, please either include that stack trace or the incorrectly ordered QuerySet. — glotchimo, Nov 14 '19 at 17:59
Could you show the results that it gave? That would be helpful in determining a pattern and thereby finding the specific issue. — glotchimo, Nov 14 '19 at 18:02

Satendra · Accepted Answer · 2019-11-14T18:10:08.997

3

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.

edited Nov 14 '19 at 18:10

answered Nov 14 '19 at 18:05

Satendra

6,755
4
26
46

just sake of curiosity, can you add the equivalent SQL query? – JPG Nov 14 '19 at 18:10
@JPG just check this [SO question](https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by) for detail – Satendra Nov 14 '19 at 18:13

Unique text message objects ordered by date query Django ORM

1 Answers1