Clever Geek Handbook

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?


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

Thank you in advance!

+2


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)
+1


source


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
Mail.objects.filter(conversation_id__exact=42).order_by('-id')[0]


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.

0


source






More articles:


All Articles