mysql - Doctrine、Symfony3 使用间隔从表中选择日期范围

Question

为了让一切清楚，我有这两个表

table 1
-------------------------------
id | name | email | created_at 
x  | x    |  x    | 2016:09:01
x  | x    |  x    | 2016:09:01
x  | x    |  x    | 2016:09:01
x  | x    |  x    | 2016:09:02
x  | x    |  x    | 2016:09:04
x  | x    |  x    | 2016:09:04
-------------------------------

<小时/>

table 2
-------------------------------
id | name | email | created_at
x  | x    |  x    | 2016:09:03
x  | x    |  x    | 2016:09:03
x  | x    |  x    | 2016:09:05
-------------------------------

用户必须选择两个日期开始和结束假设他/她选择了开始 = 2016:09:01 和结束 = 2016:09:07所以我需要像这样显示结果

result
-------------------------------
num_of_recs_t1 | num_of_recs_t2 | day
3              | 0              | 2016:09:01
1              | 0              | 2016:09:02
0              | 2              | 2016:09:03
2              | 0              | 2016:09:04
0              | 1              | 2016:09:05
0              | 0              | 2016:09:06
0              | 0              | 2016:09:07

我使用 Symfony 3 的原则，说实话，我什至不认为我可以用普通的 mysql 命令来实现

提前致谢

Answer 1

在 Controller 中尝试这个

$db = $this->getDoctrine()->getConnection();

$data = $db->fetchAll("
select
  t1.num_of_recs as num_of_recs_t1,
  t2.num_of_recs as num_of_recs_t2,
  t1.created_at
from
  (
    select created_at, count(*) as num_of_recs
    from table_1
    group by created_at
  ) t1
  join (
    select created_at, count(*) as num_of_recs
    from table_2
    group by created_at
  ) t2
    on t1.created_at = t2.created_at
order by
  t1.created_at");

dump($data);