Databases
StackOverflow2013
Postsdbo
PODistinct-style filtering on a Django model
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODistinct-style filtering on a Django model
    text
    copied!<p>Distinct might be the wrong word for what I want but I have a <code>Message</code> class like the following for a simple flat messaging system between users:</p> <pre><code>class Message(models.Model): thread = models.ForeignKey(Thread) from_user = models.ForeignKey(User, related_name='messagefromuser') to_user = models.ForeignKey(User, related_name='messagetouser') when = models.DateTimeField(auto_now_add=True) message = models.TextField() </code></pre> <p>This allows two users to chat about a single Thread object. The system is designed to allow two users to have separate conversations on separate Threads.</p> <p>So as it is, I can grab the messages a given user is involved in with the following query:</p> <pre><code>Message.objects.filter( Q(from_user=u) | Q(to_user=u) ) </code></pre> <p>That outputs every message a user has sent or received. I'm building a page where users can see all their conversations with other users, grouped by thread. This is the <em>ideal</em> output that I can imagine getting to:</p> <pre><code>[ { 'thread': thread_instance, 'conversations': [ { 'other_user': user_instance 'latest_reply': date_time_instance }, .... ] }, ... ] </code></pre> <p>I have thought about iterating this from the top, but unless there's a way to filter through <code>Thread</code> into <code>Message</code>'s <code>to_user</code>, <code>from_user</code> fields, there are just too many threads. The DB server would melt. </p> <ul> <li>"Group" the messages by <code>Thread</code></li> <li>"Group" those by the other user so each group is between two distinct users, per <code>Thread</code></li> <li>Pluck the most recent <code>to_user=u</code> and annotate something with that.</li> </ul> <p>I'm going a little crazy trying to warp my brain around the particulars. In my head it feels like something you should be able to do in a couple of lines but I just can't see how.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload