How to get total users signed up per day using query builder

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

etc.

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->userRepository->createQueryBuilder('user')
            ->orderBy('user.created', 'ASC')
            ->getQuery();

        $adapter   = new DoctrineAdapter(new ORMPaginator($qb));
        $paginator = new Paginator($adapter);

        return $paginator->setCurrentPageNumber((int) 1)->setItemCountPerPage((int) $days);
    }

EDIT:

This returns the results without them being grouped / counted:

    $qb   = $this->userRepository->createQueryBuilder('user')
        ->select('SUBSTRING(user.created, 1, 10) as date')
        ->getQuery();

    return $qb->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

I could of course parse the returned array and count each item however that seems like a hack.

score 0 · Answer 1 · answered Jun 26 '17 at 14:49

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->userRepository->createQueryBuilder('user')
            ->select('SUBSTRING(user.created, 1, 10) as date, COUNT(user.id) as users')
            ->groupBy('date')
            ->orderBy('date','ASC')
            ->setMaxResults($days)
            ->getQuery();

        return $qb->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

    }

How to get total users signed up per day using query builder

1 Answers1