MySQL + symphony querybuilder with paginator limit : wrong number of entities

My database : MySQL Project : Symfony 6

Table structure : 1 article may have many comments each article must have 1 article.

Steps :

1 - I can list my articles filtered by user roles.

2 - I can list my articles and their comments.

3 - I can use a paginator.

Problem: BUT when 1 article got 2 comments with the first version of the query, the limit of the paginator gets 2 rows containing the same article in both lines, with the first comment on line 1, and the second comment on line 2. So my first page only contains the first article with two comments, instead of two articles with all their comments.

So I need to use a subquery. Problem : with MySQL, we can't use limit inside a subquery. The workaround is to use inner join instead...

For now I managed to create a (working I think) query :

SELECT *
FROM article AS a
INNER JOIN (
  SELECT id AS requestedId
  FROM article
  WHERE role_requis = 'PUBLIC_ACCESS'
  ORDER BY requestedId ASC
  LIMIT 2 OFFSET 1
) AS article
ON article.requestedId = a.id
LEFT JOIN comment
ON comment.article_id = article.requestedId;

From you though, is there any way to make it in a shorter way ? And if not I'm struggling to convert this query into a symfony querybuilder or dql... even with the documentation.

Thank you if you answer and try to help

EDIT :

Manage to find another way without LIMIT and OFFSET here is the final SQL query I have to convert into queryBuilder or DQL :

SELECT filteredArticle.*, comment.*, user.*
  FROM (
    SELECT row_number() OVER (ORDER BY article.id) AS articleNumber, article.*
    FROM article
    WHERE article.role_requis IN ('PUBLIC_ACCESS')
    GROUP BY article.id
    ORDER BY article.id ASC)
    AS filteredArticle
LEFT JOIN comment ON comment.article_id = filteredArticle.id
JOIN user ON filteredArticle.auteur_id = user.id
WHERE (articleNumber >= 1 AND articleNumber <= 2);

Where (articleNumber > 1 and articleNumber < 2) :

Explaination : 1 = (currentPageNumber * limit) - limitByPage + currentPageNumber and 2 = currentPageNumber * limitByPage

1. It is generating a row number representing the selected article number in the subquery

2. Add the article columns (ordered, filtered)

3. Join article's autor and comments

4. limit the subquery based on the generated articleNumber

It's still missing the subrequest total filteredArticle number...

EDIT :

Managed to add the max subrequest records with a cross join... but it's duplicating the main subrequest...

SELECT filteredArticle.totalRecordsNumber, filteredArticle.articleNumber, filteredArticle.id AS articleId, filteredArticle.titre, filteredArticle.corps, filteredArticle.date AS articleDate, filteredArticle.role_requis, comment.id as commentID, comment.contenu, comment.date AS commentDate, comment.auteur_id as commentAuteurId, user.id as articleAuteurID
FROM (
    SELECT *
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY article.id) AS articleNumber, article.*
        FROM article
        WHERE article.role_requis IN ('PUBLIC_ACCESS'))
        AS filteredArticleWithoutTotal
    CROSS JOIN (
        SELECT Max(articleWihSelectedRoles.recordsNumber)
        AS totalRecordsNumber
        FROM (
                SELECT ROW_NUMBER() OVER (ORDER BY article.id)
                AS recordsNumber
                FROM article
                WHERE article.role_requis
                IN ('PUBLIC_ACCESS'))
                AS articleWihSelectedRoles)
        AS total)
    AS filteredArticle
LEFT JOIN comment ON comment.article_id = filteredArticle.id
JOIN user ON filteredArticle.auteur_id = user.id
WHERE articleNumber >= 1 AND articleNumber <= 2
ORDER BY filteredArticle.id;

Making another thread to convert in query builder ...

As I wrote in the main post with edits, I found a way to get all needed informations from the database to paginate withotu limit/offset

1. I'm generating a pseudo ID with row_number in the subquery to determine how many articles are fetched and use this number in a where close as limit.

2. I'm duplicating the query to get the max number of records there is in the subquery (to calculate how many page in the repository).

3. I get My X number of articles, their informations, their comments, their users, ... and the total number of articles in the subquery.

BUT : Doctrine doesn't support row_number() eather...

SO I ended up using knpPaginator :

 $result = [];

            $query = $this->createQueryBuilder('article');
                $query->andWhere($query->expr()->in('article.role_requis', Roles::getGrantedRoles($user)))
                ->leftJoin('article.comments', 'comments')
                ->leftJoin('article.auteur', 'articleAuteur')
                ->leftJoin('comments.auteur', 'commentAuteur')
                ->addSelect('articleAuteur')
                ->addSelect('comments')
                ->addSelect('commentAuteur')
                ->orderBy('article.id', 'ASC')
            ;

            $datas = $this->paginator->paginate(
                $query, 
                $currentPageNumber, 
                $limitByPage
            );
            
        $result['datas'] = $datas->getItems();

        $result['totalPagesNumber'] = ceil($datas->getTotalItemCount() / $limitByPage);