Django DB API equivalent to a somewhat complex SQL query

I am new to Django and still have some problems with simple queries.

Suppose I am writing an email application. This is the Mail Model:

class Mail(models.Model):
    to = models.ForeignKey(User, related_name = "to")
    sender = models.ForeignKey(User, related_name = "sender")
    subject = models.CharField()
    conversation_id = models.IntegerField()
    read = models.BooleanField()
    message = models.TextField()
    sent_time = models.DateTimeField(auto_now_add = True)


Each mail has a message_id that identifies the set of email messages that are written and replied. Now, for listing emails in my inbox, I would like gmail to only show the last email per conversation.

I have a SQL equivalent that does the job, but how do I build my own Django query for it?

        id in
        group by conversation_id); 


Thank you in advance!


source to share

2 answers

It works? This will require Django 1.1.

from django.db.models import Max
mail_list = Mail.objects.values('conversation_id').annotate(Max('id'))
conversation_id_list = mail_list.values_list('id__max',flat=True)
conversation_list = Mail.objects.filter(id__in=conversation_id_list)




So, given conversation_id

, you want to get the corresponding record with the highest id

. To do this, use order_by

to sort the results in descending order (because you want the highest to id

appear first) and then use the array syntax to get the first element to be the highest id


# Get latest message for conversation #42


However, this is different from your SQL query. As a result of your request, the last message from each conversation appears. This provides the latest message from one particular conversation. You can always make one request to get the list of conversations for that user, and then make multiple requests to get the last message from each conversation.



All Articles