Requiring a primary key in raw SQL complicates a query in Django

Question

To get the maximum value from a simple table of values, I can write the following query in Django:

MyTable.objects.aggregate(Max('value'))

Generated SQL: 'SELECT MAX("mytable"."value") AS "value__max" FROM "mytable"'

Now, if I write the same SQL using a raw query manager:

1. MyTable.objects.raw('SELECT max(value) FROM mytable')

Django throws an error InvalidQuery: Raw query must include the primary key. This is also mentioned in Django docs: "There is only one field that you cannot leave outside - the primary key field." Therefore, after adding the field idI will need GROUP BY. The new request will look like this:

2. MyTable.objects.raw('SELECT id, max(value) FROM mytable GROUP BY id')

This no longer gives me any maximum value, because I have to use it GROUP BY id. Now I need to add a ORDER BYand operator LIMITto get the expected response for another simple SQL statement that works.

3. MyTable.objects.raw('SELECT id, max(value) AS mv FROM mytable GROUP BY id ORDER BY mv DESC LIMIT 1')

Is there a way to simplify the above query, i.e. not use ORDER / LIMIT / GROUP BY (FWIW using PosgreSQL)?

Update:

Here is a hack that will work. I mean the maximum value idto make Django happy. Is there a problem here?

MyTable.objects.raw('SELECT max(value) AS id FROM mytable')

Update 2:

Here's the query plan for simple SQL (1) and complex final (3):

"Aggregate  (cost=5.25..5.26 rows=1 width=2) (actual time=0.155..0.155 rows=1 loops=1)"
"  ->  Seq Scan on mytable  (cost=0.00..4.60 rows=260 width=2) (actual time=0.018..0.067 rows=260 loops=1)"
"Total runtime: 0.222 ms"

"Limit  (cost=9.80..9.80 rows=1 width=6) (actual time=0.548..0.548 rows=1 loops=1)"
"  ->  Sort  (cost=9.80..10.45 rows=260 width=6) (actual time=0.545..0.545 rows=1 loops=1)"
"        Sort Key: (max(value))"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  HashAggregate  (cost=5.90..8.50 rows=260 width=6) (actual time=0.328..0.432 rows=260 loops=1)"
"              ->  Seq Scan on mytable  (cost=0.00..4.60 rows=260 width=6) (actual time=0.018..0.069 rows=260 loops=1)"
"Total runtime: 0.638 ms"

PS . The actual request is more complex (somewhat related to this answer: https://dba.stackexchange.com/a/86404/52114 )

Answer 1

SQL Manager.raw():

from django.db import connection

cursor = connection.cursor()
cursor.execute('SELECT max(value) FROM mytable')
max_value = cursor.fetchone()[0]

Answer 2

U

ModelName.objects.raw('SELECT 1 as id , max(value) FROM mytable')

Answer 3

I would do something like:

select id, value from mytable order by value desc limit 1