[Django]-Raw query must include the primary key

by

19👍

✅

I reproduced the same problem using Python 2.7.5, Django 1.5.1 and Mysql 5.5.

I’ve saved the result of the raw call to the results variable, so I can check what columns it contains:

>>> results.columns
['ID', 'CHARACTER_ID', 'MESSAGE', 'occurrences']

ID is in uppercase, so in your query I changed s1.ID to s1.id and it works:

>>> results = Message.objects.raw('''
...         SELECT s1.id, s1.CHARACTER_ID, MAX(s1.MESSAGE) MESSAGE, MAX(s1.c) occurrences
...         FROM
...            (SELECT ID, CHARACTER_ID, MESSAGE, COUNT(*) c
...             FROM tbl_message WHERE ts > DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY CHARACTER_ID,MESSAGE) s1
...         LEFT JOIN
...            (SELECT ID, CHARACTER_ID, MESSAGE, COUNT(*) c
...             FROM tbl_message WHERE ts > DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY CHARACTER_ID,MESSAGE) s2
...           ON s1.CHARACTER_ID=s2.CHARACTER_ID
...          AND s1.c < s2.c
...         WHERE s2.c IS NULL
...         GROUP BY CHARACTER_ID
...         ORDER BY occurrences DESC''', [days, days])
>>> results.columns
['id', 'CHARACTER_ID', 'MESSAGE', 'occurrences']
>>> results[0]
<Message_Deferred_character_id_location_id_message_ts: Character object: hello...>
👤dusan

32👍

Include 1 as id to your query 

Message.objects.raw('''
        SELECT 1 as id , s1.ID, s1.CHARACTER_ID, MAX(s1.MESSAGE) MESSAGE, MAX(s1.c) occurrences
        FROM
           (SELECT ID, CHARACTER_ID, MESSAGE, COUNT(*) c
            FROM tbl_message WHERE ts > DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY CHARACTER_ID,MESSAGE) s1
        LEFT JOIN
           (SELECT ID, CHARACTER_ID, MESSAGE, COUNT(*) c
            FROM tbl_message WHERE ts > DATE_SUB(NOW(), INTERVAL %s DAY) GROUP BY CHARACTER_ID,MESSAGE) s2
          ON s1.CHARACTER_ID=s2.CHARACTER_ID
         AND s1.c < s2.c
        WHERE s2.c IS NULL
        GROUP BY CHARACTER_ID
        ORDER BY occurrences DESC''', [days, days])
👤Tinashe Robert

5👍

Make Sure the primary key is part of the select statement.

Example:

This will not work:

`Model.objects.raw("Select Min(id), rider_id from Table_Name group by rider_id")`

But this will work:

`Model.objects.raw("Select id, Min(id), rider_id from Table_Name group by rider_id")`
👤Muhammad Zahid

3👍

For those also stuck with this problem, perhaps like me, wondering why Django needs a pk, when you don’t have a pk for the query (eg you want multiple rows) – Django just needs an id field returned, the pk does not need to be part of a where clause. ie:

select * from table where foo = 'bar';

or

select id, description from table where foo = 'bar';

Both of these work, if there is a field id in the table. But this throws the error described by Thomas Schwärzl, because no id field is returned:

select description from table where foo = 'bar';

👤typonaut

Leave a comment