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!

David · Accepted Answer · 2009-09-04 09:12:52Z

1

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)

answered Sep 4, 2009 at 9:12

David

1,6791 gold badge12 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nate · Accepted Answer · 2009-09-03 22:20:41Z

0

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.

answered Sep 3, 2009 at 22:20

Nate

19.1k9 gold badges51 silver badges54 bronze badges

1 Comment

edkirin Over a year ago

Thanks for the answer but this is not what I need. I don't want to manually fetch values for each conversation, because I need single Django query for later pagination.

Collectives™ on Stack Overflow

Django DB API equivalent of a somewhat complex SQL query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related