使用 Paginator 进行数据库分页查询时,如果有 extra(where=sql) 限制查询范围,同时用 .values() 限定输出字段,就会出现类似下面的错误:
no such column id:
检查后台生成的SQL, 可以看到下面这样奇怪的SQL语句:
SELECT COUNT(*) FROM
(SELECT "t_MenuItem"."id", "t_MenuItem"."name", "t_MenuItem"."descrip"
FROM "t_MenuItem"
WHERE ("t_MenuItem"."name" LIKE %% ESCAPE '\' OR
"t_MenuItem"."descrip" LIKE %% ESCAPE '\' ) AND
id NOT IN (SELECT item_id FROM t_GroupPermissions
WHERE group_id = 2)) A1 WHERE id NOT IN
(SELECT item_id FROM t_GroupPermissions WHERE group_id = 2)
<class 'sqlite3.OperationalError'>
no such column: id
注意就是这一句造成了no such column 错误:
A1 WHERE id NOT IN
(SELECT item_id FROM t_GroupPermissions WHERE group_id = 2)
同样的程序在Django 0.97SVN运行一直正常,可以判定这是Django 1.0出现的缺陷。