Doctrine: Count left joined with condition rows

  collections, doctrine, symfony

I created symfony 5 project, which contains user entity with reports (OneToMany relation). I created table with users data using knp paginator. Now I wanna add possibility to order users data by number of last month reports (before I used criteria to get this number, but users data couldn’t be ordered by this number). So I added leftJoin reports to users data and count it inside query. Everything works fine, but when I added limitation joined reports to last month(‘r.createdAt BETWEEN :now AND :lastMonth) result is always ‘0’ to all users.

Code of getting data of users by user repository:

    public function findAllQuery(string $searchTerms): Query

        if ($searchTerms) {
            return $this->searchByTermsQuery($searchTerms);

        return $this->createQueryBuilder('u')
            /* This join counts reports correctly but it counts all reports by user (without date limitation)
            ->leftJoin('u.reports', 'r')
            ->leftJoin('u.reports', 'r', ExprJoin::WITH, 'r.createdAt BETWEEN :now AND :lastMonth')
            ->addSelect('COUNT( AS monthReports')
                'now' => new DateTime('now'),
                'lastMonth' => new Datetime('last month')

And pagination code:

    <th {% if pagination.isSorted('monthReports') %} class="sorted" {% endif %}>
        {{ knp_pagination_sortable(pagination, 'Month reports', 'monthReports') }}
        {{ user.monthReports }}

Can You tell me what I did wrong??

Source: Symfony Questions