Django grouping records on column

Question

Consider the following model:

class Message(models.Model):
    other_model = models.ForeignKey(OtherModel) # OtherModel has owner field
    thread_user = models.ForeignKey(User)
    posting_user = models.ForeignKey(User)

    message = models.TextField()

What I'm trying to do is have private messaging between two users on a third object, in this case, a user's profile. Ie User A can go to User B's profile and post a message. In the above model:

other_model = OtherModel instance belonging to User A
thread_user = User B
posting_user = User B

If User A wants to reply, only posting_user changes. This allows multiple private conversation threads between User A and other users.

But I'm having problems with display logic. At the moment, I just pull the comments out sorted by pk I get something like this:

Message.objects.all():
[
    Message<other_model=a, thread_user=b, posting_user=b>, # B to A
    Message<other_model=a, thread_user=b, posting_user=a>, # A to B
    Message<other_model=a, thread_user=c, posting_user=c>, # C to A
    Message<other_model=a, thread_user=b, posting_user=b>, # B to A
    Message<other_model=a, thread_user=c, posting_user=c>, # C to A again
    Message<other_model=a, thread_user=b, posting_user=a>, # A to B again
    Message<other_model=a, thread_user=b, posting_user=a>, # A to C
]

I would like to group them by thread_user so that all messages with B and all messages from C were separate. Ideally in a dict like this:

{
    User<b>: [
        Message<other_model=a, thread_user=b, posting_user=b>, # B to A
        Message<other_model=a, thread_user=b, posting_user=a>, # A to B
        Message<other_model=a, thread_user=b, posting_user=b>, # B to A
        Message<other_model=a, thread_user=b, posting_user=a>, # A to B again
    ]
    User<c>: [
        Message<other_model=a, thread_user=c, posting_user=c>, # C to A
        Message<other_model=a, thread_user=c, posting_user=c>, # C to A again
        Message<other_model=a, thread_user=b, posting_user=a>, # A to C
    ]
}

There's an element of sorting too (so the freshest thread comes out on top) but I'm not worrying about that for now.

---

Edit: Okay so I've managed to do this in 2n+1 queries (where n is the number of "threads" linked to an OtherModel instance):

other_model = "<a's OtherModel instance>"
tree = []
for thread in Message.objects.filter(other_model=other_model).values_list('thread_user', flat=True).distinct():
    user = User.objects.get(pk=thread)
    tree.append({'user':user, 'messages':Message.objects.filter(other_model=other_model, thread_user=user)})

My problem with this is it takes 2n+1 queries. Ideally I'd be doing this in one ugly query. Is this as slimline as I'm ever going to get?

Answer 1

This isn't really a question of grouping. It's just a matter of knowing that any relation - ie the one from Message to User - automatically has a backwards relation. So what you actually need to do is to get the list of thread users, then get the messages for each one.

Unfortunately, you have't posted the actual working model code, as when you have two ForeignKeys from one model to another you always need to define a related_name attribute, and you haven't shown us what that is. I'll assume it is thread_user_message. You also haven't shown us how you get the threads or users you are interested in. so I'll have to guess:

users = User.objects.filter(message__other_model=a)
for user in users:
    print user.thread_user_message.all()

Or, you could do the last two lines in your template:

{% for user in users %}
    {% for message in user.thread_user_message.all %}
        {{ message }}
    {% endfor %}
{% endfor %}