SQL code conversion into a single Django ORM query?

Question

I'm trying to convert the following SQL query in to a single Django query in order to minimize number of executed sql queries, however I'm not able to do that except using a for-loop.

I appreciate it if anyone could provide me the intended Django query

SQL Query:

select FUND_ID, REPORT_DATE, PURCHASE_NAV_PER_SHARE
FROM FUNDS_HISTORY
WHERE REPORT_DATE = (
   SELECT Max(REPORT_DATE)
   FROM FUNDS_HISTORY fund_history
   where fund_history.FUND_ID = FUNDS_HISTORY.FUND_ID
)

models:

class Fund(models.Model):
    name_en = models.CharField(max_length=256, blank=True, null=True)


class History(models.Model):
    fund = models.ForeignKey('funds.Fund', on_delete=models.CASCADE, blank=True, null=True)
    purchase_nav_per_share = models.BigIntegerField(blank=True, null=True)
    report_date = models.DateField(blank=True, null=True)

Please share the related models.

willeM_ Van Onsem
– willeM_ Van Onsem

2019-08-05 12:40:35 +00:00
Commented Aug 5, 2019 at 12:40 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 5, 2019 at 12:40
please edit your question.

willeM_ Van Onsem
– willeM_ Van Onsem

2019-08-05 12:46:56 +00:00
Commented Aug 5, 2019 at 12:46 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 5, 2019 at 12:46

willeM_ Van Onsem · Accepted Answer · 2019-08-05 14:07:42Z

1

We can annotate the History objects with the maximum of the related Fund, like:

History.objects.annotate(
    max_date=Max('fund__history__report_date')
).filter(
    report_date=F('max_date')
)

or a bit simpler:

History.objects.filter(
    report_date=Max('fund__history__report_date')
)

You can slightly improve performance with:

from django.db.models import Max, OuterRef, Subquery

History.objects.filter(
    report_date=Subquery(
        History.objects.filter(
            fund_id=OuterRef('fund_id')
        ).values('fund_id').annotate(
            last_date=Max('report_date')
        ).values('last_date')[:1]
    )
)

This will result in a query that looks like:

SELECT history.id, history.fund_id, history.purchase_nav_per_share, history.report_date
FROM history
WHERE history.report_date = (
    SELECT MAX(U0.report_date) AS last_date
    FROM history U0
    WHERE U0.fund_id = history.fund_id
    GROUP BY U0.fund_id
    LIMIT 1
)

The above is quite similar to the query in the question.

We here obtain the History objects, which is usually better than grasping the values itself since that is a sort of "primitive obsession" antipattern.

edited Aug 5, 2019 at 14:07

answered Aug 5, 2019 at 13:17

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

8 Comments

Erfan Over a year ago

actually I want the value of purchase_nav_per_share related to the maximum report date for every fund_id.

willeM_ Van Onsem Over a year ago

@Erfan: but you get that: the History objects from this queryset of course have a .purchase_nav_per_share attribute.

Erfan Over a year ago

the translation of your suggestion is a complex SQL query and I couldn't get the output according to its high execution time

willeM_ Van Onsem Over a year ago

@Erfan: it has two JOINs: one is on the fund model, which is always a single one, then another history, which will again result in some extra values. But in general both the queries in the question and answer should run in O(n^2) with n the number of Historys.

Erfan Over a year ago

I'm not sure but Django may translating it in an improper way which is the following sql code. I am using django debug toolbars to retrieve sql query

|

Shaheer Raza · Accepted Answer · 2019-08-05 13:01:31Z

0

You can translate above sql query as django ORM.

History.objects.annotate(report_date_max=Max('report_date')).filter(report_date=F('report_date_max')).values_list('fund_id', 'report_date', 'purchase_nav_per_share')

Model field names might be incorrect as you have not shared related field names.

edited Aug 5, 2019 at 13:01

answered Aug 5, 2019 at 12:56

Shaheer Raza

491 gold badge1 silver badge9 bronze badges

2 Comments

Erfan Over a year ago

thanks for response. your query seems to be same as following sql but I wan't the query returning one record per fund id while your suggestion does not provide my desirable output.

SELECT "funds_history"."fund_id",        "funds_history"."report_date",        "funds_history"."purchase_nav_per_share" FROM "funds_history" GROUP BY "funds_history"."id" ORDER BY "funds_history"."id" ASC

Shaheer Raza Over a year ago

try this

History.objects.values_list('fund_id').annotate(report_date_max=Max('report_date')).filter(report_date=F('report_date_max')).values_list('fund_id', 'report_date', 'purchase_nav_per_share')

Collectives™ on Stack Overflow

SQL code conversion into a single Django ORM query?

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related