Databases
StackOverflow2013
Postsdbo
PODjango - finding the extreme member of each group
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODjango - finding the extreme member of each group
    primarykey
    data
    text
    <p>I've been playing around with the new aggregation functionality in the Django ORM, and there's a class of problem I <em>think</em> should be possible, but I can't seem to get it to work. The type of query I'm trying to generate is described <a href="http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/" rel="nofollow noreferrer">here</a>.</p> <p>So, let's say I have the following models -</p> <pre><code>class ContactGroup(models.Model): .... whatever .... class Contact(models.Model): group = models.ForeignKey(ContactGroup) name = models.CharField(max_length=20) email = models.EmailField() ... class Record(models.Model): contact = models.ForeignKey(Contact) group = models.ForeignKey(ContactGroup) record_date = models.DateTimeField(default=datetime.datetime.now) ... name, email, and other fields that are in Contact ... </code></pre> <p>So, each time a Contact is created or modified, a new Record is created that saves the information as it appears in the contact at that time, along with a timestamp. Now, I want a query that, for example, returns the most recent Record instance for every Contact associated to a ContactGroup. In pseudo-code:</p> <pre><code>group = ContactGroup.objects.get(...) records_i_want = group.record_set.most_recent_record_for_every_contact() </code></pre> <p>Once I get this figured out, I just want to be able to throw a <code>filter(record_date__lt=some_date)</code> on the queryset, and get the information as it existed at <code>some_date</code>.</p> <p>Anybody have any ideas?</p> <p>edit: It seems I'm not really making myself clear. Using models like these, I want a way to do the following with pure django ORM (no extra()):</p> <pre><code>ContactGroup.record_set.extra(where=["history_date = (select max(history_date) from app_record r where r.id=app_record.id and r.history_date &lt;= '2009-07-18')"]) </code></pre> <p>Putting the subquery in the where clause is only one strategy for solving this problem, the others are pretty well covered by the first link I gave above. I know where-clause subselects are not possible without using extra(), but I thought perhaps one of the other ways was made possible by the new aggregation features.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODjango - finding the extreme member of each group
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy do you have a foreign key to a ContactGroup from both Record and Contact models? Also, a minor nitpick, but using auto_now or auto_now_add on your DateTimeField instead of explicitly specifying the default is usually more convenient.
      singulars
      1. PODjango - finding the extreme member of each group
      1. USoggy
    2. COThere is a foreign key from Record because it is an exact copy of Contact, plus the foreign key to contact and the record_date. It's a snapshot of a Contact at a particular time. Also, auto\_now and auto\_now\_add are known to cause hard-to-diagnose bugs, are disliked by the core committers, and are hardly more convenient than datetime.datetime.now
      singulars
      1. PODjango - finding the extreme member of each group
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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