我可以在django 1.3的orm中控制GROUP BY吗？ [英] Can I control the GROUP BY in django 1.3's orm?

问题描述

这是数据的样子：

  | project | 
 | id | name | 
 | 1 |一些项目| 
 | 2 |我的其他项目| 
 
 | run | 
 | id | project_id | start_time | result | 
 | 1 | 1 | 1305732581845 | something | 
 | 2 | 1 | 1305732593721 | nothing | 
 | 3 | 2 | 1305732343721 | nothing | 
 | 4 | 2 | 1305732556821 | something | 
  

我希望能够通过项目从每个最新的运行中获取整个记录集。 SQL查询将如下所示：

  SELECT *，MAX（run。start_time）
 FROMrun
 LEFT OUTER JOINprojectON（run。project_id=project。id）
 GROUP BYproject。id
  

这将返回两个表中的所有列，以便最新的项目运行，这是非常好的，它正是我需要。

所以在尝试在django 1.3中找到django orm等价物时，我根本无法找到正确的方法。如果我这样做：

  Run.objects.annotate（Max（'start_time'））
  

生成的SQL查询将如下所示：

  SELECT 
run。id，run，result，run，project_id，project，id，project ，
 MAX（run。start_time）
 FROMrun
 LEFT OUTER JOINprojectON（run。project_id=project。id ）
 GROUP BYrun。id，run。result，run。project_id，projectid，project。name
  

这不会给我正确的结果，因为组是不正确的我想要的。我相信以前版本的django，以下内容将正确和明确地设置查询中的group by子句，但在1.3中似乎不起作用：

  q = Run.objects.annotate（Max（'start_time'））
 q.query.group_by = [（project，id）] 
  

在1.3中，将生成完全相同的查询，而不是手动修改查询中的group_by属性。

我也尝试过这种逻辑的方法，它基于在annotate（）调用之前和之后的.values（）行为。但它没有按预期的方式工作。当我尝试这个：

  q = Run.objects.values（'project__id'）。annotate（Max（'start_time'） ）.values（'id'）
  

我最终得到这样的查询：

  SELECT 
run。id，run。project_id
 MAX（run start_time）
 FROMrun
 LEFT OUTER JOINprojectON（run。project_id=project。id）
 GROUP BYrun id，run。project_id
  

任何人都可以指出我正确的做法我在做什么没有以下任何一种：

• 使用raw sql - 当我不断地使用orm时，会是什么意思生成自己的查询？


• 使用.extra（select = {'latest'：'somequery'}） - 为什么在没有子查询的完美有效查询可以给我的情况下，我必须使用子查询我想要的。


• 再次使用多个查询来获取相同的数据 - 为什么我必须多次查询才能获得结果在1中可用

解决方案

tl; dr：Django允许您控制group by子句，但它限制了它在所有风格的SQL中工作，所以我不能做我想要的。

已经指出，原来查询我正在尝试使用django生成ORM实际上并不适用于所有风格的SQL。这是我正在寻找的查询的更新：

  SELECT *，MAX（run。start_time）
 FROMrun
 LEFT OUTER JOINprojectON（run。project_id=project。id）
 GROUP BYprojectid
  

如果一个人尝试选择不在MSSQL中的GROUP BY中的东西，它们实际上会收到错误。所以在我看来，django实际上不应该让我生成一个这样的查询，我本质上试图解决我的问题错误。

I think this will best be explained with an example.

Here is what the data would looks like:

|project            |
|id|name            |
|1 |some project    |
|2 |my other project|

|run                                  |
|id|project_id|start_time   |result   |
|1 |1         |1305732581845|something|
|2 |1         |1305732593721|nothing  |
|3 |2         |1305732343721|nothing  |
|4 |2         |1305732556821|something|

I would like to be able to get an entire recordset from each of the latest runs by project. The SQL Query would look something like this:

SELECT *, MAX("run"."start_time")
FROM "run"    
LEFT OUTER JOIN "project" ON ("run"."project_id" = "project"."id") 
GROUP BY "project"."id"

This will return me all columns across both tables for the latest run of the project, which is great, it is exactly what I need.

So in attempting to find the django orm equivalent in django 1.3 I simply can't find a proper way to do it. If I do something like this:

Run.objects.annotate(Max('start_time'))

The generated SQL query will look something like:

SELECT 
"run"."id", "run"."result", "run"."project_id", "project"."id", "project"."name", 
MAX("run"."start_time")
FROM "run"
LEFT OUTER JOIN "project" ON ("run"."project_id" = "project"."id")
GROUP BY "run"."id", "run"."result", "run"."project_id", "project"."id", "project"."name"

This will not return me the proper results as the group by is incorrect for what I want. I believe in previous versions of django the following would properly and explicitly set the group by clause in the query but appears to not work in 1.3:

q = Run.objects.annotate(Max('start_time'))
q.query.group_by = [("project", "id")]

In 1.3 this generates the exact same query as not manually modifying the group_by property in the query.

I also tried this the logical way based on the documented behavior of .values() before and after the annotate() call but it did not work as expected. When I tried this:

q = Run.objects.values('project__id').annotate(Max('start_time')).values('id')

I ended up with a query like this:

SELECT 
"run"."id", "run"."project_id"
MAX("run"."start_time")
FROM "run"
LEFT OUTER JOIN "project" ON ("run"."project_id" = "project"."id")
GROUP BY "run"."id", "run"."project_id"

Can anyone point me to the correct way to do what I am doing without any of the following:

• Using raw sql - what would be the point of using an orm when I constantly have to generate my own queries?
• Using .extra(select = {'latest': 'somequery'}) - why should I have to use subqueries when a perfectly valid query without subqueries can give me what I want.
• Using multiple queries to grab the same data - again, why should I have to make multiple queries to get results that are available in 1?

解决方案

tl;dr: Django does allow you to control the group by clause but it limits it to work across all flavors of SQL so I can't do what I want.

It has been pointed out to me that the original query I am trying to generate with the django ORM is not actually valid for all flavors of SQL. Here is a refresher of the query I was looking for:

SELECT *, MAX("run"."start_time")
FROM "run"    
LEFT OUTER JOIN "project" ON ("run"."project_id" = "project"."id") 
GROUP BY "project"."id"

If a person attempts to select something that is not in the GROUP BY in MSSQL they will actually get an error. So it seems to me that django actually shouldn't let me generate a query like this and I am essentially attempting to solve my problem incorrectly.

这篇关于我可以在django 1.3的orm中控制GROUP BY吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！