[Answered ]-Django ORM query for a messaging system



I added a method in a model manager for this:

    def get_active_threads(self, user):
        all_threads = MessageRecipient.objects.filter(recipient=user).order_by('-message__timestamp')

        deleted_threads = []
        final_threads = []

        for thread in all_threads:
            if thread.thread not in (deleted_threads or final_threads):
                if thread.status == "deleted":

        return final_threads


You could either do it in python (as suggested above) or let the DB do the dirty work for you.

You would need to construct a QuerySet containing all threads with a deleted message for a particular user and then a second QuerySet that gets all threads not contained in the first:

delthreads = MessageThread.objects.filter(messagerecipient__recipient=user,
result = MessageThread.objects.filter(messagerecipient_recipient=user)

However, performance wise (especially on large tables) this will hurt you. Your model needs some restructuring to allow for more efficient queries. Consider:

class MessageThread(models.Model):
    subject = models.CharField(max_length=256, blank=False)

class Message(models.Model):
    thread = models.ForeignKey(MessageThread)
    content = models.CharField(max_length=5000, blank=False)
    timestamp = models.DateTimeField(auto_now_add=True, blank=False)
    sender = models.ForeignKey(User)
    recipient = models.ForeignKey(User)
    status = models.CharField(max_length=20, choices=MESSAGE_STATUS, default="unread")

If you anticipate you will execute this query often, you could add a cache field to MessageThread which you can update whenever someone deletes a message in that thread:

class MessageThread(models.Model):
    subject = models.CharField(max_length=256, blank=False)
    dirty = models.BooleanField(default=False)

Then, it would be very easy to extract a list of threads with no deleted messages.

nondelthreads = MessageThread.objects.filter(message__recipient=user, dirty=True).distinct()


As a quick aside, why have you got thread as a field within MessageRecipient? You can get to the thread via the message.

Anyhow, you need to use exclude()

# breaking up onto multiple lines for readability, not valid python though
๐Ÿ‘คJosh Smeaton

Leave a comment