StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First of all, I'll point out that:</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 <= '2009-07-18')"]) </code></pre> <p>will not get you the same effect as:</p> <pre><code>records_i_want = group.record_set.most_recent_record_for_every_contact() </code></pre> <p>The first query returns every record associated with a particular group (or associated with any of the contacts of a particular group) that has a record_date less than the date/ time specified in the extra. Run this on the shell and then do this to review the query django created:</p> <pre><code>from django.db import connection connection.queries[-1] </code></pre> <p>which reveals:</p> <pre><code>'SELECT "contacts_record"."id", "contacts_record"."contact_id", "contacts_record"."group_id", "contacts_record"."record_date", "contacts_record"."name", "contacts_record"."email" FROM "contacts_record" WHERE "contacts_record"."group_id" = 1 AND record_date = (select max(record_date) from contacts_record r where r.id=contacts_record.id and r.record_date <= \'2009-07-18\') </code></pre> <p>Not exactly what you want, right?</p> <p>Now the aggregation feature is used to retrieve aggregated data and not objects associated with aggregated data. So if you're trying to minimize number of queries executed using aggregation when trying to obtain <em>group.record_set.most_recent_record_for_every_contact()</em> you won't succeed.</p> <p>Without using aggregation, you can get the most recent record for all contacts associated with a group using:</p> <pre><code>[x.record_set.all().order_by('-record_date')[0] for x in group.contact_set.all()] </code></pre> <p>Using aggregation, the closest I could get to that was:</p> <pre><code>group.record_set.values('contact').annotate(latest_date=Max('record_date')) </code></pre> <p>The latter returns a list of dictionaries like:</p> <pre><code>[{'contact': 1, 'latest_date': somedate }, {'contact': 2, 'latest_date': somedate }] </code></pre> <p>So one entry for for each contact in a given group and the latest record date associated with it.</p> <p>Anyway, the minimum query number is probably 1 + # of contacts in a group. If you are interested obtaining the result using a single query, that is also possible, but you'll have to construct your models in a different way. But that's a totally different aspect of your problem.</p> <p>I hope this will help you understand how to approach the problem using aggregation/ the regular ORM functions.</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