CODEWITHSUNDEEP
pythondjangodjango-models
David542
David542

Reputation: 110257

Django ORM query for a messaging system

I have the following models for an internal messaging app:

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)

class MessageRecipient(models.Model):
    message = models.ForeignKey(Message)
    thread = models.ForeignKey(MessageThread)
    recipient = models.ForeignKey(User)
    status = models.CharField(max_length=20, choices=MESSAGE_STATUS, default="unread")

For a given user, to get a list of threads I am doing:

distinct_threads=MessageThread.objects.filter(messagerecipient__recipient=user)
    .order_by('-message__timestamp').distinct()

How would I get rid of all threads with a message that contains the status "deleted" in the ORM? (In other words, I want to get a list of all threads without the thread containing a message with the status="deleted".)

Upvotes: 1

Views: 191

Answers (3)

enticedwanderer
enticedwanderer

Reputation: 4346

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

Upvotes: 1

David542
David542

Reputation: 110257

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

Upvotes: 1

Josh Smeaton
Josh Smeaton

Reputation: 48730

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

Upvotes: 0

Related Questions