A raw request must include a primary key

Question

I got the original SQL statement in my views.py

 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]) 

The result of this SQL statement (directly tested in the database):

 ID | CHARACTER_ID | MESSAGE | OCCURENCES ----+--------------+---------+-------------- 148 | 10 | test | 133 

But all I got is an InvalidQuery Exception with Raw query must include the primary key information Raw query must include the primary key

Then I double-checked the docs and read:

There is only one field that you cannot ignore - the primary key field .... An InvalidQuery exception will be raised if you forget to include the primary key.

As you can see, I received the requested primary key added to my statement. What's wrong?

 class Message(models.Model): character = models.ForeignKey('Character') message = models.TextField() location = models.ForeignKey('Location') ts = models.DateTimeField() class Meta: pass def __unicode__(self): return u'%s: %s...' % (self.character, self.message[0:20]) 

Answer 1

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

I saved the result of calling raw results variable, so I can check which columns it contains:

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

ID is in upper case, so in your request 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...> 

Answer 2

Include 1 as id in your request

 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])