[Answered ]-Django ORM query for a messaging system

by

1👍

✅

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":
                    deleted_threads.append(thread.thread)
                else:
                    final_threads.append(thread)

        return final_threads
👤David542

1👍

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,
    messagerecipient__status='deleted').distinct()
result = MessageThread.objects.filter(messagerecipient_recipient=user)
    .exclude(messagethread_in=delthreads).distinct()

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()
👤enticedwanderer

0👍

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
MessageThread.objects.filter(messagerecipient__recipient=user)
.exclude(messagerecipient__status='deleted')
.order_by('-message__timestamp').distinct()
👤Josh Smeaton

Leave a comment