Title: How to get total users signed up per day using query builder
Tags: zend-framework2;query-builder;doctrine-query
Question: I have a user table with a created datetime field. What I want to do is parse the table and return how many users have signed up per day:
12/06/2017 | 5
12/06/2017 | 8
12/06/2017 | 7
12/06/2017 | 12
I figured it would be best to use the pagination class to optimize the query and so far have this:
public function getUserSignups($days)
$qb   = $this-&gt;userRepository-&gt;createQueryBuilder('user')
-&gt;orderBy('user.created', 'ASC')
-&gt;getQuery();
$adapter   = new DoctrineAdapter(new ORMPaginator($qb));
$paginator = new Paginator($adapter);
return $paginator-&gt;setCurrentPageNumber((int) 1)-&gt;setItemCountPerPage((int) $days);
This returns the results without them being grouped / counted:
$qb   = $this-&gt;userRepository-&gt;createQueryBuilder('user')
-&gt;select('SUBSTRING(user.created, 1, 10) as date')
-&gt;getQuery();
return $qb-&gt;getResult(\Doctrine\ORM\Query75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0HYDRATE_ARRAY);
I could of course parse the returned array and count each item however that seems like a hack.


Here is another answer: I figured it out, hopefully this will help others who wish to use QueryBuilder to return a count of users signed up over a time period:
public function getUserSignups($days)
$qb   = $this-&gt;userRepository-&gt;createQueryBuilder('user')
-&gt;select('SUBSTRING(user.created, 1, 10) as date, COUNT(user.id) as users')
-&gt;groupBy('date')
-&gt;orderBy('date','ASC')
-&gt;setMaxResults($days)
-&gt;getQuery();
return $qb-&gt;getResult(\Doctrine\ORM\Query75b5:f2c1:f5d7:b954:8dc3:cee9:baf:a8c0HYDRATE_ARRAY);

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:06/26/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<zend-framework2><query-builder><doctrine-query>', 'Title': 'How to get total users signed up per day using query builder', 'ViewCount': 39, 'id': 44760611, 'user_AboutMe': '<p>I work as a business developer for a foundation, we work on projects that are aimed at helping people create small business in the tech space by using the "power of the crowd" to significantly reduce costs.</p>\n\n<p>One of our projects is <a href="https://www.gamblingtec.com" rel="nofollow noreferrer" title="Casino Investment Partners">https://www.gamblingtec.com</a> which provides a platform for game developers and casino operators to enter the online gaming space without the need to re-mortgage their homes in order to get going.</p>\n\n<p>We also manage and run a cloud hosting facility in Curacao, <a href="https://www.curacaowebhosting.com" rel="nofollow noreferrer" title="offshore webhosting &amp; gambling hosting">https://www.curacaowebhosting.com</a> which has been designed to offer an affordable offshore location for business to host their web presence.</p>\n', 'user_AccountId': 3350019.0, 'user_DisplayName': 'HappyCoder', 'user_Reputation': 5707.0, 'user_Views': 390.0, 'user_id': 2814241.0}}