原始查询必须包含主键

本文介绍了原始查询必须包含主键的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在我的 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...>