CODEWITHSUNDEEP
djangopostgresqldjango-orm
Artem Bernatskyi
Artem Bernatskyi

Reputation: 4677

Rewrite PostgreSQL to Django ORM

if this possible to rewrite this PostgreSQL query to Django ORM with no raw SQL, i tried it by myself but i haven't succeeded.

Here is Fund's model 

class Fund(models.Model):
    name = models.CharField(max_length=320, unique=True)
    fund_manager = models.CharField()
     ..............

And here is NAV's model 

class NAV(models.Model):
    price = models.DecimalField(max_digits=9, verbose_name=_('NAV'))
    date = models.DateField(verbose_name=_('date'))
    fund = models.ForeignKey('Fund', related_name='navs')
    created_date = models.DateTimeField(default=now)

And here is sql query that works and needs to be rewritten to Django ORM

select * from finance_fund ORDER BY(
  (select price from finance_nav where date='2016-11-08' and fund_id=finance_fund.id) / 
  (select price from finance_nav where date='2016-11-07' and fund_id=finance_fund.id)
)

So basically i need to order by division result of two simple queries of backward relations of Foreign key

Thanks in advance !

Upvotes: 2

Views: 127

Answers (1)

Udi
Udi

Reputation: 30522

Only with raw SQL. Try something like:

qs = Fund.objects.annotate(x=RawSQL("""
    (select price from finance_nav where finance_fund.id=fund_id and date=%s limit 1) 
    / (select price from finance_nav where finance_fund.id=fund_id and date=%s limit 1)
    """,('2016-11-08', '2016-11-07'))).order_by('x')

Upvotes: 1

Related Questions