Re: Trouble expressing a query in the ORM

On Fri, Sep 9, 2011 at 9:28 AM, Pewpewarrows <marco.chomut@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 and p.export_date = p.max_export_date;

--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.