0

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'}
CompanyCode.objects.extra(select=ratios)
# 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.

0

Ok, I found it out. In above using:

```
CompanyCode.objects.select_related('quarter').extra(select=ratios)
```

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 :).

1

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:

```
@property
def quarter(self):
return self.sales_now / self.sales_previous
```

and call it where necessary

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

1

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
CompanyCode.objects.annotate(
growth=F('quarter__sales_now') / F('quarter__sales_previous'),
)
```

Note this might not work with django 1.7. For more information: https://code.djangoproject.com/ticket/1403 - yunshi 2016-10-11 13:29