I think you are on something here. Below is what concerns me most in the example:
data[o.name].append(o.model3_set.all().order_by('created')[0]) This will sort the entire model3 table just to get the min value. And if the db is doing this 6000 times, that is a bit useless. Have you tried using raw SQL in you model? See: http://docs.djangoproject.com/en/dev/topics/db/sql/#topics-db-sql You do not have to use the Django ORM for this. Maeck On Dec 8, 7:43 am, DavidA <[EMAIL PROTECTED]> wrote: > If I undestand the problem correctly, in MySQL you could do this in > one query as: > > select > m.*, > (select min(created) from model2 where id = m.model2_id) as > first_created, > (select max(created) from model2 where id = m.model2_id) as > last_created > from model1 m > ; > > I don't know how that translates to the Django ORM (or even if it > does), though. > > -Dave > > On Dec 8, 12:27 am, Malcolm Tredinnick <[EMAIL PROTECTED]> > wrote: > > > On Sat, 2008-12-06 at 20:56 -0800, erikcw wrote: > > > Hi all, > > > > I'm trying to write a model query that will return a queryset along > > > with the latest (and earliest) data from 2 related models. > > > > Right now I'm doing something like this: > > > > objects = Model1.objects.filter(user=3).select_related() #about 6,000 > > > objects > > > > data = {} > > > for o in objects: > > > data[o.name] = [o.field1, o.field2] > > > data[o.name].append(o.field3.model2_set.all().latest('created')) > > > #get latest row from related model2 > > > data[o.name].append(o.model3_set.all().order_by('created')[0]) > > > #get earliest row from related model3 > > > > The problem is that this results in a TON of database queries. This > > > view is taking over a minute to process. The select_related on the > > > first line doesn't seem to be helping since I'm using latest()/ > > > order_by which generates a new query. > > > > How can I make this more efficient? Denormalizing the isn't an option > > > since model2 and model 3 are many-to-one. > > > By the way (since I still haven't worked out the complex SQL to make it > > three queries yet), this last statement isn't correct. Denormalising > > doesn't just mean flattening. You can store a computed dependent field > > in model1 for the related information. So you could, for example, store > > the relevant date and pk value of the related model2 row and update that > > whenever a new model2 is inserted. > > > Malcolm --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---