Django DB API equivalent of a somewhat complex SQL query

Question

I'm new to Django and still having some problems about simple queries.

Let's assume that I'm writting 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 conversation_id which identifies a set of email messages which are written and replyed. Now, for listing emails in inbox, I would like as gmail to show only last email per conversation.

I have the SQL equivalent which does the job, but how to construct native Django query for this?

select
        *
from
        main_intermail
where
        id in
        (select
                max(id)
        from
                main_intermail
        group by conversation_id); 

Thank you in advance!

Answer 1

So, given a conversation_id you want to retrieve the related record which has the highest id. To do this use order_by to sort the results in descending order (because you want the highest id to come first), and then use array syntax to get the first item, which will be the item with the highest id.

# Get latest message for conversation #42
Mail.objects.filter(conversation_id__exact=42).order_by('-id')[0]

However, this differs from your SQL query. Your query appears to provide the latest message from every conversation. This provides the latest message from one specific conversation. You could always do one query to get the list of conversations for that user, and then follow up with multiple queries to get the latest message from each conversation.

Answer 2

Does this work? It would 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)