Nested selects in django queries

[Amir Meirbekov]

Hi everyone!

How can I do nested SELECTs in django queries, like in the following SQL?

SELECT anon_1.timestamp AS anon_1_timestamp, max(anon_1.users) AS users, max(anon_1.servers) AS servers
FROM (
 SELECT datediff_hours(:datediff_hours_1, statistics.time) AS timestamp,
   sum(statistics.users) AS users,
   sum(statistics.servers) AS servers
 FROM statistics
 WHERE statistics.gameid = :gameid_1 AND
          datediff_hours(:datediff_hours_1, statistics.time) >= datediff_hours(:datediff_hours_2, :datediff_hours_3) AND
          datediff_hours(:datediff_hours_1, statistics.time) <= datediff_hours(:datediff_hours_4, :datediff_hours_5)
 GROUP BY statistics.time
 ) AS anon_1
GROUP BY anon_1.timestamp

My model looks like this:

class Statistics(models.Model): 
    game_id = models.IntegerField(db_column='gameid', primary_key=True) 
    time = models.DateTimeField() 
    servers = models.IntegerField()
    users = models.IntegerField()

`datediff_hours` in SQL is a database function and can be called the following way:

from django.db.models import Func, Value, F

class DateDiffHoursFunc(Func):
      function = 'datediff_hours'

query = Statistics.objects.annotate(timestamp=DateDiffHoursFunc(Value(datetime(2016, 5, 26)), F('time')))