Doctrine: Count left joined with condition rows

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(r.id) AS monthReports')
            ->setParameters([
                'now' => new DateTime('now'),
                'lastMonth' => new Datetime('last month')
            ])
            ->groupBy('u.id')
            ->getQuery()
        ;
    }

And pagination code:

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

Can You tell me what I did wrong??

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *