Doctrine maxResult issue

Question

I have a query with multiple inner joins and a maxResults on my query. The issue is that when I set the maxResults I also counts the results from the inner join.

For example I have prediction_summary 200 records each has 4 prediction records. I wil get only 25 prediction_summary records back. I was expecting 200 records.

Doctrine code this is part of a repository.

Can someone help?

public function findByForIndex(
        ?int $client = null,
        ?int $article = null,
        ?int $year = null,
        int $page = 0,
        int $type = 0,
    ): mixed {
        $queryBuilder = $this->createQueryBuilder('ps');
        $queryBuilder
            ->select('ps','p')
            ->join('ps.predictions','p')
            ->where('ps.type = 0')
            ->orderBy('ps.client')
            ->addOrderBy('ps.article')
            ->addOrderBy('ROW_NUMBER() OVER (PARTITION BY ps.client, ps.article ORDER BY ps.orderYear)')
            ->addOrderBy('ps.orderYear')
            ->setMaxResults(self::PAGE_LIMIT)
            ->setFirstResult($page > 1 ? $page * self::PAGE_LIMIT : 0);

        if (!is_null($client)) {
            $queryBuilder->andWhere('pc.client = :clientID')->setParameter('clientID', $client);
        }

        if (!is_null($article)) {
            $queryBuilder->andWhere('pc.article = :articleID')->setParameter('articleID', $article);
        }

        if (1 === $type) {
            $queryBuilder->andWhere('pc.recurring > 0');
        } elseif (2 === $type) {
            $queryBuilder->andWhere('pc.incidental > 0');
        }

        return $queryBuilder->getQuery()->getResult();
    }

The query

SELECT p0_.id                  AS id_0,
       p0_.order_confirmed     AS order_confirmed_1,
       p0_.recurring           AS recurring_2,
       p0_.incidental          AS incidental_3,
       p0_.order_year          AS order_year_4,
       p0_.type                AS type_5,
       p0_.created_at          AS created_at_6,
       p0_.updated_at          AS updated_at_7,
       p1_.id                  AS id_8,
       p1_.order_year          AS order_year_9,
       p1_.order_quater        AS order_quater_10,
       p1_.prediction          AS prediction_11,
       p1_.is_agreed           AS is_agreed_12,
       p1_.description         AS description_13,
       p1_.override_prediction AS override_prediction_14,
       p1_.type                AS type_15,
       p1_.created_at          AS created_at_16,
       p1_.updated_at          AS updated_at_17,
       p0_.article_id          AS article_id_18,
       p0_.client_id           AS client_id_19,
       p1_.client_id           AS client_id_20,
       p1_.article_id          AS article_id_21
FROM predictions_summary p0_
         INNER JOIN prediction_summary_join p2_ ON p0_.id = p2_.summary_id
         INNER JOIN prediction p1_ ON p1_.id = p2_.prediction_id
WHERE p0_.type = 0
ORDER BY p0_.client_id ASC, p0_.article_id ASC, ROW_NUMBER() OVER ( ORDER BY p0_.order_year ASC) ASC, p0_.order_year ASC
LIMIT 100

I tried running the query manually that then I got the expected result 200 prediction_summary with 4 predictions.

Answer 1

found a solution for this issue, i had to add distinct

$queryBuilder
->select('ps') 
->distinct()
->join('ps.predictions', 'p') 
->join('p.orders', 'po')
->where('ps.type = 0')
->orderBy('ps.client')
->addOrderBy('ps.article') 
->addOrderBy('ROW_NUMBER() OVER (PARTITION BY ps.client, ps.article ORDER BY ps.orderYear)')
->addOrderBy('ps.orderYear')
->setMaxResults(self::PAGE_LIMIT)
->setFirstResult($page > 1 ? $page * self::PAGE_LIMIT : 0)