CODEWITHSUNDEEP
pythondjango
Mirage
Mirage

Reputation: 31548

How to make this complex query in django python

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

  1. Get all the plan objects
  2. Now based on the state and city each plan will have many rows in weather table
  3. 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)')

Upvotes: 0

Views: 101

Answers (1)

Koobz
Koobz

Reputation: 6938

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/

Upvotes: 2

Related Questions