On 09.09.2011 17:36, Daniel Gagnon wrote:
On Fri, Sep 9, 2011 at 9:28 AM, Pewpewarrows <marco.cho...@gmail.com
<mailto:marco.cho...@gmail.com>> wrote:
Tim Shaffer's response would have you doing N+1 queries, and
having to loop through all of your Target objects in-memory.
Technically it would work, but as soon as you have a decently
sized amount of data in there it'd slow to a crawl.
Indeed and I have to sort and pick a subset of the sort after that. I
can't run a loop through thousands and thousands of items to only
select a hundred.
The best way I can see to do this straight with the Django ORM is:
prop_dates =
Target.objects.annotate(latest_property=Max('property__export_date')).values_list('latest_property',
flat=True)
properties = Property.objects.filter(export_date__in=prop_dates)
Let say I have two targets which I'll call target1 and target2.
Target1 have a property for yesterday and one for today. Target2 only
have a property for yesterday. Both yesterday and today will be
included in prop_dates since they are the latest for at least one
target. And then Target1 will have two entries in properties while it
should only have one for today.
Which comes out to two queries. If you absolutely had to, you
could execute some raw SQL to narrow it down to one query. Please
note that there is a (very) small possibility that the second
query might return extra Properties for a given Target. Because
it's operating based on date-timestamps, there could be two
Properties that happen to have the exact same export_date, one of
which happens to be the most recent for a given Target.
Eliminating those duplicates, if you want to account for that
scenario, can be done in-memory or I believe through some
adjustments to the second line above.
I just designed a SQL query, I am wondering if I couldn't translate
the logic to the ORM. It looks like this:
select * from Target_Mgmt_target as t
inner join (select *,max(export_date) as max_export_date from
Target_Mgmt_property group by target_id) as p on p.target_id = t.id
<http://t.id> and p.export_date = p.max_export_date;
--
from django.db.models import Max
Target_Mgmt_target.objects.annotate(export_date=Max('Target_Mgmt_property__export_date')).values(<vals
you want>)
This should work
--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
