How do I convert this complex SQL into a Django model query?

  • A+
Category:Languages

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,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?

mysql> select     ->     s.symbol,     ->     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'     -> order by     ->     s.symbol asc; +--------+--------------+-----------+ | symbol | trading_date | low       | +--------+--------------+-----------+ | A      | 2018-05-03   | 105.00000 | | C      | 2018-05-04   |  90.00000 | +--------+--------------+-----------+ 2 rows in set (0.02 sec) 


EDIT: I managed to reform the solution using Django subqueries.

We can translate the query to Django ORM using Django's aggregates with SubQuery expressions:

  1. Create a subquery to retrieve the lowest close for every symbol:

    from django.db.models import OuterRef, Subquery, Min       lows = StockHistory.objects.filter(     stock=OuterRef('stock'),      trading_date__gte='2017-05-04' ).values('stock__symbol') .annotate(low=Min('close')) .filter(trading_date__gte='2018-04-30') 
    • Breakdown:

      • filter the queryset to get only the stocks with trading_date >= '2017-05-04'.
      • "GROUP BY" stock__symbol (examples of group by in Djnago: GROUP BY ... MIN/MAX, GROUP BY ... COUNT/SUM).
      • annotate the lowest (low) price to every element.
      • filter the queryset again to get only the objects with a low field occurring on trading_date >= '2018-04-30'.
    • Intermediate Result:

      Although we cannot get a result at this stage, the subquery will look like this:

      [     {'stock__symbol': 'A', 'low': Decimal('105.00000')},                 {'stock__symbol': 'C', 'low': Decimal('90.00000')} ] 

      We are missing the trading_date.

  2. Utilize the subquery to retrieve the specific StockHistory objects:

    StockHistory.objects.filter(     stock__symbol=Subquery(lows.values('stock__symbol')),     close=Subquery(lows.values('low')),     trading_date__gte='2018-04-30' ).values('stock__symbol', 'trading_date', 'close') .order_by('stock__symbol') 
    • Breakdown:

      • lows.values('stock__symbol') and lows.values('low') retrieve the respective values from the subquery.
      • filter the queryset against the lows subquery values. Also filter against the specified date in order to eliminate low close prices occurring before that date.
      • Get the specified values.
      • Order the result by stock__symbol (by default ascending).
    • Result:

      [     {         'close': Decimal('105.00000'),          'trading_date': datetime.date(2018, 5, 3),          'stock__symbol': 'A'     },      {         'close': Decimal('90.00000'),          'trading_date': datetime.date(2018, 5, 4),          'stock__symbol': 'C'     } ] 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: