On Fri, Sep 9, 2011 at 9:28 AM, Pewpewarrows <[email protected]> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
