I need to get paginated greatest scores for all users, but I have problem with paginator.

I have a score table and AppEntityScore entity. The table has id, value, user_id, created_at and updated_at. The users table has id, name, created_at and updated_at.

#[ORMEntity(repositoryClass: ScoreRepository::class)]
#[ORMHasLifecycleCallbacks]
class Score extends BaseEntity implements JsonSerializable
{
    #[ORMId]
    #[ORMGeneratedValue]
    #[ORMColumn]
    private ?int $id = null;

    #[ORMColumn]
    private ?int $value = null;

    #[ORMManyToOne(inversedBy: 'scores')]
    #[ORMJoinColumn(nullable: false)]
    private ?User $user = null;

    /** other methods */
}

$qb = $this->createQueryBuilder('s');
$subquery = $this->createQueryBuilder('b')
    ->select('MAX(b.id)')
    ->where(
        'b.value = (
            SELECT MAX(a.value)
            FROM AppEntityScore a
            WHERE a.user = b.user
        )'
    )
    ->groupBy('b.user')
    ->getDQL();

$qb
    ->select('s')
    ->orderBy('s.value', 'DESC')
    ->where($qb->expr()->in('s.id', $subquery))
    ->setMaxResults($perPage)
    ->setFirstResult(($page - 1) * $perPage);

This code is incorrect, because it generates me a query with nonsense select statmement, when I use paginator.

(new Paginator($qb))->getIterator();

Error: An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘s1_.id’ in ‘field list’

SELECT DISTINCT id_0
FROM ( SELECT DISTINCT id_0, value_1
       FROM ( SELECT s0_.id         AS id_0,
                     s0_.value      AS value_1,
                     s0_.created_at AS created_at_2,
                     s0_.updated_at AS updated_at_3,
                     s1_.id         AS id_4,
                     s1_.value      AS value_5,
                     s1_.created_at AS created_at_6,
                     s1_.updated_at AS updated_at_7
              FROM score s0_
              WHERE s0_.id IN ( SELECT MAX(s1_.id) AS sclr_8
                                FROM score s1_
                                WHERE s1_.value =
                                      ( SELECT MAX(s2_.value) AS sclr_9 FROM score s2_ WHERE s2_.user_id = s1_.user_id )
                                GROUP BY s1_.user_id ) ) dctrn_result_inner
       ORDER BY value_1 DESC ) dctrn_result
LIMIT 10;

But if I get a raw sql from the same query, the SQL query will be correct.

dd($qb->getQuery()->getSQL());

SELECT s0_.id         AS id_0,
       s0_.value      AS value_1,
       s0_.created_at AS created_at_2,
       s0_.updated_at AS updated_at_3,
       s0_.user_id    AS user_id_4
FROM score s0_
WHERE s0_.id IN ( SELECT MAX(s1_.id) AS sclr_5
                  FROM score s1_
                  WHERE s1_.value = ( SELECT MAX(s2_.value) AS sclr_6 FROM score s2_ WHERE s2_.user_id = s1_.user_id )
                  GROUP BY s1_.user_id )
ORDER BY s0_.value DESC
LIMIT 10;

How can I fix my query builder in order to work with paginator? Do you have any code examples of using Doctrine pagination and greatest n per group?