教义：按日期范围组合 [英] Doctrine: Group by date ranges

问题描述

在我的Symfony应用程序中，我有一个实体Project，它包含两个字段：'createdOn'（type = date）和'individual'。个人可以在项目中多次出现。

  _created_On_ | _individual_id 
 2012.12.01 | 3 
 2012.12.24 | 5 
 2013.01.10 | 9 
  

我正在尝试构建一个查询，以createdOn分组的所有不同个体一种方式，我得到按月排序的结果。并且必须设置查询的日期范围。

我的查询到目前为止：

 'SELECT p.createdOn，COUNT（DISTINCT p.individual）
 FROM ... \DossierBundle\Entity\Project p 
 WHERE p.createdOn 
 BETWEEN：name1 
 AND：name2'
） - > setParameters（array（
'name1'=> $ startDate，
'name2'=> $ endDate， 
））
  

这不太让我想到下面的结果

  _DATE ____ | _Number_of_Individuals 
 2012年12月| 2 
 2013年1月| 1 
  

但是我得到

  __ DATE _____ | _Number_of_Individuals 
 2012.12.01 | 1 
 2012.12.24 | 1 
 2013.01.10 | 1 
  

Google没有帮助我，所以任何支持都将不胜感激。

Flo

解决方案

您需要使用自定义日期函数来扩展原则，并且要谨慎，因为你不能使用组合功能，所以你必须欺骗学说。

看看：

http://www.doctrine-project.org/blog /doctrine2-custom-dql-udfs.html

这里是一天本机mysql功能的例子：

https：// github。 com / beberlei / DoctrineExtensions / blob / master / lib / DoctrineExtensions / Query / Mysql / Day.php

，并按照问题和工作周围（使用as）：

http://www.doctrine-project.org/jira/browse/DDC-1236

我需要按日期分组访问（没有时间），所以我写了一个带有日期扩展名的dql查询：

  select count（v）as visit_count，DATE（v.created_at）as day_created_at from Shorten\Entity\Visit v group by day_created_at 
  

希望它有所帮助。

In my Symfony app I have an entity 'Project' which contains two fields: 'createdOn' (type = date) and 'individual'. An individual can occur multiple times in 'Project'.

_created_On_|_individual_id
 2012.12.01 |     3
 2012.12.24 |     5
 2013.01.10 |     9

I'm trying to build a query to count all distinct individuals grouped by 'createdOn' in such a way, that I get results sorted by month. And it must be possible to set a date range for the query.

My query so far:

'SELECT p.createdOn, COUNT (DISTINCT p.individual) 
                FROM ...\DossierBundle\Entity\Project p
                WHERE p.createdOn
                BETWEEN :name1
                AND :name2' 
                )->setParameters(array(
                'name1' => $startDate,
                'name2' => $endDate,
                ))

This doesn't quite get me the desired result below

_DATE____|_Number_of_Individuals
Dec 2012 | 2
Jan 2013 | 1

But instead I get

__DATE_____|_Number_of_Individuals
2012.12.01 |     1
2012.12.24 |     1
2013.01.10 |     1 

Google didn't help me either so any support will be much appreciated.

Flo

解决方案

You need to extend doctrine with custom dates functions , and be carefull because you cant use group by with functions so you'll have to trick doctrine.

have a look at that :

http://www.doctrine-project.org/blog/doctrine2-custom-dql-udfs.html

here is an exemple of a day native mysql function :

https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/Day.php

and read that for the group by issue and work around ( using as ) :

http://www.doctrine-project.org/jira/browse/DDC-1236

i needed to group visits by date ( without the time ) , so i wrote that dql query with a date extension :

select count(v) as visit_count , DATE(v.created_at) as day_created_at from Shorten\Entity\Visit v group by day_created_at 

hope it helps.

这篇关于教义：按日期范围组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！