我在我的 views.py 中有一个原始的SQL语句
I got a raw 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])此SQL语句的结果(直接在数据库上测试)是:
The result of this SQL statement (tested on database directly) is:
ID | CHARACTER_ID | MESSAGE | OCCURENCES ----+--------------+---------+-------------- 148 | 10 | test | 133但是我得到的只是一个 InvalidQuery 信息的异常原始查询必须包含主键
But all I got is a InvalidQuery Exception with the information Raw query must include the primary key
然后我重新检查了 docs ,并阅读:
Then I double checked the docs and read:
只有一个字段,您不能遗漏 - 主键字段....如果您忘记包含主要的关键。
There is only one field that you can’t leave out - the primary key field....An InvalidQuery exception will be raised if you forget to include the primary key.
正如你所看到的,我在我的声明中添加了所请求的主键。怎么了?
As you can see I got the requested primary key added in 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])推荐答案
我使用Python 2.7.5,Django 1.5.1和Mysql 5.5。
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 是大写字母,所以在您的查询中,我将 s1.ID 更改为 s1.id ,它的作品:
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...>更多推荐
原始查询必须包含主键
发布评论