这是我的学说（1.2）查询的第一种形式，在页面底部是返回的内容。
$q = Doctrine_Query::create()
->select('i.id, i.time_estimate, i.assigned_to, ts.time_spent')
->from('Model_Issue i')
->innerJoin('i.User_2 user')
->leftJoin('i.IssuesTimeSpent ts')
->where('i.project_id = ?',$project_id);
我正在尝试这样做：
$q = Doctrine_Query::create()
->select('i.id, sum(i.time_estimate) as time_estimate, i.assigned_to, sum(ts.time_spent) as time_spent')
->from('Model_Issue i')
->innerJoin('i.User_2 user')
->leftJoin('i.IssuesTimeSpent ts')
->where('i.project_id = ?',$project_id)
->groupBy('assigned_to');
我期望有两个用户（assigned_to）的回报，其中所有时间都花费在与assigned_to id相关的所有问题上，与time_estimate相同。
但这就是返回的内容。
array
0 =>
array
'id' => string '1' (length=1)
'assigned_to' => string '1' (length=1)
'time_estimate' => string '238' (length=3) <- this is wrong
'time_spent' => string '72' (length=2) <- this is correct i believe
1 =>
array
'id' => string '7' (length=1)
'assigned_to' => string '2' (length=1)
'time_estimate' => string '3' (length=1) <- this is wrong
'time_spent' => string '30' (length=2) <- this is correct i believe
我在尝试正确的方法吗，仅使用groupby和合计值可以实现我想要的输出吗？还是我需要做一些特别的事情？
问候，
N.B. groupby / sum之前的完整数组
array
0 =>
array
'id' => string '1' (length=1)
'time_estimate' => string '4' (length=1)
'assigned_to' => string '1' (length=1)
'IssuesTimeSpent' =>
array
0 =>
array
'id' => string '1' (length=1)
'time_spent' => string '2' (length=1)
1 =>
array
'id' => string '40' (length=2)
'time_spent' => string '1' (length=1)
1 =>
array
'id' => string '2' (length=1)
'time_estimate' => string '6' (length=1)
'assigned_to' => string '1' (length=1)
'IssuesTimeSpent' =>
array
0 =>
array
'id' => string '2' (length=1)
'time_spent' => string '4' (length=1)
1 =>
array
'id' => string '5' (length=1)
'time_spent' => string '3' (length=1)
2 =>
array
'id' => string '6' (length=1)
'time_spent' => string '3' (length=1)
3 =>
array
'id' => string '8' (length=1)
'time_spent' => string '1' (length=1)
4 =>
array
'id' => string '9' (length=1)
'time_spent' => string '1' (length=1)
5 =>
array
'id' => string '10' (length=2)
'time_spent' => string '1' (length=1)
6 =>
array
'id' => string '11' (length=2)
'time_spent' => string '1' (length=1)
7 =>
array
'id' => string '12' (length=2)
'time_spent' => string '2' (length=1)
8 =>
array
'id' => string '13' (length=2)
'time_spent' => string '1' (length=1)
9 =>
array
'id' => string '14' (length=2)
'time_spent' => string '1' (length=1)
10 =>
array
'id' => string '15' (length=2)
'time_spent' => string '1' (length=1)
11 =>
array
'id' => string '16' (length=2)
'time_spent' => string '1' (length=1)
12 =>
array
'id' => string '17' (length=2)
'time_spent' => string '1' (length=1)
13 =>
array
'id' => string '18' (length=2)
'time_spent' => string '1' (length=1)
14 =>
array
'id' => string '19' (length=2)
'time_spent' => string '1' (length=1)
15 =>
array
'id' => string '20' (length=2)
'time_spent' => string '1' (length=1)
16 =>
array
'id' => string '21' (length=2)
'time_spent' => string '1' (length=1)
17 =>
array
'id' => string '22' (length=2)
'time_spent' => string '1' (length=1)
18 =>
array
'id' => string '23' (length=2)
'time_spent' => string '1' (length=1)
19 =>
array
'id' => string '24' (length=2)
'time_spent' => string '1' (length=1)
20 =>
array
'id' => string '25' (length=2)
'time_spent' => string '1' (length=1)
21 =>
array
'id' => string '26' (length=2)
'time_spent' => string '1' (length=1)
22 =>
array
'id' => string '27' (length=2)
'time_spent' => string '1' (length=1)
23 =>
array
'id' => string '28' (length=2)
'time_spent' => string '1' (length=1)
24 =>
array
'id' => string '29' (length=2)
'time_spent' => string '1' (length=1)
25 =>
array
'id' => string '30' (length=2)
'time_spent' => string '1' (length=1)
26 =>
array
'id' => string '31' (length=2)
'time_spent' => string '1' (length=1)
27 =>
array
'id' => string '32' (length=2)
'time_spent' => string '1' (length=1)
28 =>
array
'id' => string '33' (length=2)
'time_spent' => string '1' (length=1)
29 =>
array
'id' => string '34' (length=2)
'time_spent' => string '1' (length=1)
30 =>
array
'id' => string '35' (length=2)
'time_spent' => string '1' (length=1)
31 =>
array
'id' => string '36' (length=2)
'time_spent' => string '1' (length=1)
32 =>
array
'id' => string '37' (length=2)
'time_spent' => string '1' (length=1)
33 =>
array
'id' => string '38' (length=2)
'time_spent' => string '1' (length=1)
34 =>
array
'id' => string '39' (length=2)
'time_spent' => string '3' (length=1)
35 =>
array
'id' => string '42' (length=2)
'time_spent' => string '1' (length=1)
36 =>
array
'id' => string '43' (length=2)
'time_spent' => string '10' (length=2)
2 =>
array
'id' => string '3' (length=1)
'time_estimate' => string '4' (length=1)
'assigned_to' => string '1' (length=1)
'IssuesTimeSpent' =>
array
0 =>
array
'id' => string '3' (length=1)
'time_spent' => string '3' (length=1)
1 =>
array
'id' => string '41' (length=2)
'time_spent' => string '10' (length=2)
3 =>
array
'id' => string '7' (length=1)
'time_estimate' => string '3' (length=1)
'assigned_to' => string '2' (length=1)
'IssuesTimeSpent' =>
array
0 =>
array
'id' => string '7' (length=1)
'time_spent' => string '30' (length=2)
最佳答案
好的，我相信我已经找到了解决方案。这是我所不知道的最好的..
$q = Doctrine_Query::create()
->select('i.id, assigned.full_name as full_name, count(i.id) as issue_count, sum(i.time_estimate) as time_estimate,
(SELECT sum(ts.time_spent) as t FROM Model_IssuesTimeSpent ts WHERE ts.user_id = i.assigned_to) as time_spent')
->from('Model_Issue i')
->leftJoin('i.User_2 assigned')
->where('i.project_id = ?',$project_id)
->groupBy('assigned_to');
为时间花费添加子查询，而不是加入它，将停止所有奇数。
似乎直接加入它会导致我正在设置的组内出现重复的行。
关于php - MYSQL Group By具有连接和汇总值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15367915/