"SELECT...AS..." with related model data in Django

Go To StackoverFlow.com


I have an application where users select their own display columns. Each display column has a specified formula. To compute that formula, I need to join few related columns (one-to-one relationship) and compute the value.

The models are like (this is just an example model, actual has more than 100 fields):

class CompanyCode(models.Model):
    """Various Company Codes"""

    nse_code = models.CharField(max_length=20)
    bse_code = models.CharField(max_length=20)
    isin_code = models.CharField(max_length=20)    

class Quarter(models.Model):
    """Company Quarterly Result Figures"""

    company_code = models.OneToOneField(CompanyCode)
    sales_now = models.IntegerField()
    sales_previous = models.IntegerField()

I tried doing:

ratios = {'growth':'quarter__sales_now / quarter__sales_previous'}
# raises "Unknown column 'quarter__sales_now' in 'field list'"

I also tried using raw query:

query = ','.join(['round((%s),2) AS %s' % (formula, ratio_name)
    for ratio_name, formula in ratios.iteritems()])
companies = CompanyCode.objects.raw("""
    SELECT `backend_companycode`.`id`, %s
    FROM  `backend_companycode` 
    INNER JOIN  `backend_quarter` ON (  `backend_companycode`.`id` =  `backend_companyquarter`.`company_code_id` ) 
    """, [query])
#This just gives empty result

So please give me a little clue as to how I can use related columns preferably using 'extra' command. Thanks.

2012-04-04 06:14
by Pratyush


Ok, I found it out. In above using:


solved the problem.

Basically, to access any related model data through 'extra', we just need to ensure that that model is joined in our query. Using select_related, the query automatically joins the mentioned models.

Thanks :).

2012-04-04 10:39
by Pratyush


Since the calculation is being done on a single Quarter instance, where's the need to do it in the SELECT? You could just define a ratio method/property on the Quarter model:

def quarter(self):
    return self.sales_now / self.sales_previous

and call it where necessary

2012-04-04 06:36
by Daniel Roseman
Sorry, but the above is just an example. There are more than 100 different ratios and 100 different fields - Pratyush 2012-04-04 06:41


By now the Django documentation says that one should use extra as a last resort.

So here is a query without extra():

from django.db.models import F

    growth=F('quarter__sales_now') / F('quarter__sales_previous'),
2016-07-08 10:44
by Michael
Note this might not work with django 1.7. For more information: https://code.djangoproject.com/ticket/1403 - yunshi 2016-10-11 13:29