How can I convert this SQL statement to Django QuerySet?

Question

PLEASE NOTE: This question is a follow-up to this question I asked a few days ago. It is not a duplicate. There are slight but significant differences in the SQL Query I'm trying to model in Django and the dummy data I have loaded.

I'm writing a Python/Django application to do some stock analysis.

I have two very simple models that look like this:

class Stock(models.Model):
    symbol = models.CharField(db_index=True, max_length=5, null=False, editable=False, unique=True)

class StockHistory(models.Model):
    stock = models.ForeignKey(Stock, related_name='StockHistory_stock', editable=False)
    trading_date = models.DateField(db_index=True, null=False, editable=False)
    close = models.DecimalField(max_digits=12, db_index=True, decimal_places=5, null=False, editable=False)

    class Meta:
        unique_together = ('stock', 'trading_date')

This is the dummy data I have populated them with:

import datetime
a = Stock.objects.create(symbol='A')
b = Stock.objects.create(symbol='B')
c = Stock.objects.create(symbol='C')
d = Stock.objects.create(symbol='D')

StockHistory.objects.create(trading_date=datetime.date(2018,1,1), close=200, stock=a)
StockHistory.objects.create(trading_date=datetime.date(2018,1,2), close=150, stock=a)
StockHistory.objects.create(trading_date=datetime.date(2018,1,3), close=120, stock=a)
StockHistory.objects.create(trading_date=datetime.date(2018,4,28), close=105, stock=a)
StockHistory.objects.create(trading_date=datetime.date(2018,5,2), close=105, stock=a)
StockHistory.objects.create(trading_date=datetime.date(2018,5,3), close=105, stock=a)

StockHistory.objects.create(trading_date=datetime.date(2017,5,2), close=400, stock=b)
StockHistory.objects.create(trading_date=datetime.date(2017,11,11), close=200, stock=b)
StockHistory.objects.create(trading_date=datetime.date(2017,11,12), close=300, stock=b)
StockHistory.objects.create(trading_date=datetime.date(2017,11,13), close=400, stock=b)
StockHistory.objects.create(trading_date=datetime.date(2017,11,14), close=500, stock=b)

StockHistory.objects.create(trading_date=datetime.date(2018,4,28), close=105, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,4,29), close=106, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,4,30), close=107, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,5,1), close=108, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,5,2), close=109, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,5,3), close=110, stock=c)
StockHistory.objects.create(trading_date=datetime.date(2018,5,4), close=90, stock=c)

I want to find all the stocks that made a yearly low within the past week.

But to make this question simpler, just assume that I want to find all the stocks whose lowest point since '2017-05-04' occurred on or after '2018-04-30'. Below is the SQL I wrote to find it. It works.

But I need help figuring out what Django Query to write to get the same results as this SQL. How can I do it? The Django provided in the answer to my previous question produced 3 rows of results instead of 2.

select
    s.symbol,
    min(sh.trading_date),
    low_table.low
from
    (
        select
            stock_id,
            min(close) as low
        from
            stocks_stockhistory
        where
            trading_date >= '2017-05-04'
        group by
            stock_id
    ) as low_table,
    stocks_stockhistory as sh,
    stocks_stock as s
where
    sh.stock_id = low_table.stock_id
    and sh.stock_id = s.id
    and sh.close = low_table.low
    and sh.trading_date >= '2018-04-30'
group by
    s.symbol,
    low_table.low
order by
    s.symbol asc;
+--------+----------------------+--------------------+
| symbol | min(sh.trading_date) | min(low_table.low) |
+--------+----------------------+--------------------+
| A      | 2018-05-02           |          105.00000 |
| C      | 2018-05-04           |           90.00000 |
+--------+----------------------+--------------------+
2 rows in set (0.01 sec)

if you used the query exactly as provided in the linked answer you shouldn't get 3 lines. Did you forget any filter from the query? Have a close look. — John Moutafis, May 11 '18 at 07:14
You can see it here very clearly: https://gist.github.com/saqib-zmi/28a7e6a4f71f49bde561ac4065494f98 — Saqib Ali, May 12 '18 at 09:02
Yes, you are missing the `trading_date__gte='2018-04-30'` argument from the `filter` in your last query. — John Moutafis, May 12 '18 at 10:01
It's clearly in there on line #11: `close=Subquery(lows.values('low')), trading_date__gte=datetime.date(2018, 4, 30)`. What am I missing? I feel like I'm losing my mind! — Saqib Ali, May 13 '18 at 03:00
Sorry to ask this... If you can do it in SQL, why have Django in the picture? I would like to know what Django brings to the table in this situation. — Rick James, May 15 '18 at 17:24
Honestly, I would like to use raw sql in such situation. django orm did not give any help on readable code and querying efficiency — newlife, May 18 '18 at 15:23

Brown Bear · Accepted Answer · 2018-05-13T08:27:26.383

can you try

from stocks.models import StockHistory, Stock
from django.db.models import OuterRef, Subquery, F, Min
low = StockHistory.objects.filter(
    stock=OuterRef('stock'), trading_date__gt='2017-05-04'
    ).order_by('close')
qs = StockHistory.objects.annotate(
    low=Subquery(low.values('close')[:1])
    )
qs = qs.filter(low=F('close')).filter(trading_date__gte='2018-04-30')
qs = qs.values('stock__symbol', 'low').order_by('stock__symbol', 'low')
qs = qs.annotate(mtd=Min('trading_date'))
qs = qs.values_list('stock__symbol', 'mtd', 'low')
qs = qs.order_by('stock__symbol', 'low')

the result:

>>> qs
<QuerySet [('A', datetime.date(2018, 5, 2), Decimal('105.00000')), ('C', datetime.date(2018, 5, 4), Decimal('90.00000'))]>

the sql code is

>>> print(qs.query)
SELECT "stocks_stock"."symbol",
      (SELECT U0."close"
       FROM "stocks_stockhistory" U0
       WHERE (U0."stock_id" = ("stocks_stockhistory"."stock_id")
              AND U0."trading_date" > 2017-05-04)
       ORDER BY U0."close" ASC LIMIT 1) AS "low",
      MIN("stocks_stockhistory"."trading_date") AS "mtd"
FROM "stocks_stockhistory"
    INNER JOIN "stocks_stock"
    ON ("stocks_stockhistory"."stock_id" = "stocks_stock"."id")
WHERE (
    (SELECT U0."close"
     FROM "stocks_stockhistory" U0
     WHERE (U0."stock_id" = ("stocks_stockhistory"."stock_id") AND U0."trading_date" > 2017-05-04)
     ORDER BY U0."close" ASC LIMIT 1) = ("stocks_stockhistory"."close")
    AND "stocks_stockhistory"."trading_date" >= 2018-04-30)
GROUP BY "stocks_stock"."symbol",
        (SELECT U0."close"
         FROM "stocks_stockhistory" U0
         WHERE (U0."stock_id" = ("stocks_stockhistory"."stock_id") AND U0."trading_date" > 2017-05-04)
         ORDER BY U0."close" ASC LIMIT 1)
ORDER BY "stocks_stock"."symbol" ASC, "low" ASC

My results are different than yours. Take a look: https://gist.github.com/saqib-zmi/fba02450cc50840054edd434aaf56ec9 — Saqib Ali, May 12 '18 at 09:13
@SaqibAli sorry for my first version i was not inattentive, and create data from your first question — Brown Bear, May 13 '18 at 08:29

How can I convert this SQL statement to Django QuerySet?

1 Answers1

Linked