How to make this complex query in django python

Question

I have the model Called Plan

Plan has state and city associated with it

I have other Model called Weather which has data at variious times during per day for each city in state

Now i want to get all the Plan objects with extra dictionary called weather.

Steps are

Get all the plan objects
Now based on the state and city each plan will have many rows in weather table
i want to aggreagte all the columns .eg if i have five rows for that city and state and temperature values are 20 , 21 , 22 , 23 , 24 . then i want take the avg of all the temperatues i.e 22 and store in new dictionary in plan model

so that i can access Plan.weather.temperature and it comes 22

I can use the for loop but that will be lot db queries . can do that on one query or what ever option is possible

EDIT:

class Plan(model.model):
    name = tinymce_models.HTMLField(blank=True, null=True)
    region = models.ForeignKey(Region)
    district = models.ForeignKey(District)

 class Weather(model.model):

        region = models.ForeignKey(Region)
        district = models.ForeignKey(District)
        temp_max = models.IntegerField(blank=True, null=True, verbose_name='Max temperature (C)')
        temp_min = models.IntegerField(blank=True, null=True, verbose_name='Min temperature (C)')

FYI: its usually a good idea to paste your models code. its much more descriptive then describing in words — miki725
– miki725, Commented Nov 1, 2012 at 3:56

Koobz · Accepted Answer · 2012-11-01 06:40:42Z

2

Depends on your model but something like this:

Plan.objects.filter(region__name="CA").annotate(avg_temperature=Avg("region__weather__temp_max"))

The avg_temperature field will now be available on the objects returned by the query. This name is customizable based on the keyword argument provided to annotate.

Or take a look here: https://docs.djangoproject.com/en/dev/topics/db/aggregation/

Django has these kinds of queries built in. If the aggregation and annotation api can't facilitate this (seems like a strong candidate for working out of the box) then you have the option of writing a raw query:

https://docs.djangoproject.com/en/dev/topics/db/sql/

edited Nov 1, 2012 at 6:40

answered Nov 1, 2012 at 4:25

Koobz

6,9156 gold badges44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mirage Over a year ago

Can i do this with dictionary like annotate(myweather['avg_temperature']=Avg("weather__temperature") , myweather['avg_pressure']=Avg("weather__pressure")) and so on so that i can go further like myobject.weather.avg_temperature rather than myobject.avg_temperature

Mirage Over a year ago

and other question my weather object is not at all associated with Plan so we can't use weather__temp , we have to join the query where region and district match

Koobz Over a year ago

For the first question, no you can't. You can simply use a funkier key like weather_avg_temperature. Just blow it up later. You're using python's keyword argument syntax here. Django does some magic to make this all work.

Koobz Over a year ago

For the second question - just keep using Django's double underscores to follow relationships across models. E.g. region__weather__temperature. Peek at the query it's generating as well!

Collectives™ on Stack Overflow

How to make this complex query in django python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related