Obtain "rank" using Doctrine 2 ORM and OneToOne in Repository

Question

I'm finding it almost impossible to achieve my desired result, after so many attempts with different solutions (sub query, raw query, etc) found here and on other websites, I must ask this question.

My goal is to extract/get each "projects" rank based on their "score".

Consider "score" as int and with values like 1,2,6,4,8,10,200, etc.

The rank would be like this:

Rank - Score

1. 200
2. 10
3. 8
4. 6

For my question to be as simple and clear as possible, I renamed my actual tables/entities as below:

MainEntity (main_table):

/**
* @ORM\Id
* @ORM\Column(name="id")
* @ORM\GeneratedValue
*/
protected $id;

// other fields, un-related to this question

/**
* @ORM\OneToOne(targetEntity="Application\Entity\SecondTable", mappedBy="second_table_data")
*/
protected $second_table;

/**
* @ORM\OneToOne(targetEntity="Application\Entity\ThirdTable", mappedBy="third_table_data")
* 
*/
protected $third_table;

SecondEntity (second_table):

/**
* @ORM\Id
* @ORM\Column(name="id")
* @ORM\GeneratedValue
*/
protected $id;

// other fields, un-related to this question

/**
* @ORM\OneToOne(targetEntity="Application\Entity\SecondTable", inversedBy="second_table")
* @ORM\JoinColumn(name="project_id", referencedColumnName="id")
*/
private $second_table_data;

ThirdEntity (third_table):

/**
* @ORM\Id
* @ORM\Column(name="id")
* @ORM\GeneratedValue
*/
protected $id;

// other fields, un-related to this question

/** 
 * @ORM\Column(name="score")  
 */
protected $score;

/**
* @ORM\OneToOne(targetEntity="Application\Entity\ThirdTable", inversedBy="third_table")
* @ORM\JoinColumn(name="project_id", referencedColumnName="id")
*/
private $third_table_data;

And the repository function to select "all projects" ordered by their score:

public function findAllProjects()
{
    $entityManager = $this->getEntityManager();

    $queryBuilder = $entityManager->createQueryBuilder();

    $queryBuilder->select('u')
       ->from(MainEntity::class, 'u')
       ->leftJoin('u.third_table', 't')
       ->orderBy('t.score', 'DESC');

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

This works fine (I believe) as I get all the "projects" from main_table + second_table + third_table based on their "project_id".

However the issue is that I cannot find a way to calculate or get each project's "rank" number correctly. I also tried to use a "foreach" and use the "index" as "rank" but that will not work properly because I am using ORMPaginator so each time you click a "page" that "foreach" "index" will reset from 0.

I hope the question is clear enough and gives you a clear understanding of my problem.

Please advise how can I achieve this, and if my whole approach for this is wrong please point it out.

Every advice/hint/solution is highly appreciated.

Answer 1

First off, pay really attention on the column types. All your columns are created by doctrine as varchar(255), which isn't the ideal if those are number on which you'd want to make an order by. This is a snippet of the output of vendor/bin/doctrine-module orm:schema-tool:create --dump-sql:

CREATE TABLE main_entity (
  id VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

So, the first thing you must do is adding column types to your columns:

/**
 * @ORM\Id
 * @ORM\Column(name="id", type="integer")
 * @ORM\GeneratedValue
 */
protected $id;

I used this data:

third_table                      main_entity
+----+------------+-------+      +----+
| id | project_id | score |      | id |
+----+------------+-------+      +----+
| 1  | 1          | 8     |      | 1  |
| 2  | 2          | 10    |      | 2  |
| 3  | 3          | 6     |      | 3  |
| 4  | 4          | 200   |      | 4  |
+----+------------+-------+      +----+

Concerning the rank, which is substantially the row number, the SQL query is:

SELECT 
   ROW_NUMBER() OVER (ORDER BY t.score DESC) AS r_rank,
   t.score AS score 
FROM main_entity m 
LEFT JOIN third_table t ON m.id = t.project_id 
ORDER BY t.score DESC
-- LIMIT 2 OFFSET 2 -- Limits for pagination
without limits           with limits
+--------+-------+       +--------+-------+
| r_rank | score |       | r_rank | score |
+--------+-------+       +--------+-------+
| 1      | 200   |       | 3      | 8     |
| 2      | 10    |       | 4      | 6     |
| 3      | 8     |       +--------+-------+
| 4      | 6     |
+--------+-------+

Unluckily, ROW_NUMBER is not implemented for SQL queries. If you try this:

$queryBuilder->select(['ROW_NUMBER() OVER (ORDER BY t.score DESC)','t.score'])
        ->from(MainEntity::class, 'u')
        ->leftJoin('u.third_table', 't')
        ->orderBy('t.score', 'DESC');
$queryBuilder->getQuery()->getResult();

You'll get the following error: [Syntax Error] line 0, col 7: Error: Expected known function, got 'ROW_NUMBER' An alternative could be:

$sql = 'SELECT 
            ROW_NUMBER() OVER (ORDER BY t.score DESC) AS r_rank,
            t.score AS score 
        FROM main_entity m 
        LEFT JOIN third_table t ON m.id = t.project_id 
        ORDER BY t.score DESC';
$results = $this->entityManager->getConnection()->executeQuery($sql);

I see that there is something similar in the paginator walkers (?!), but they are not supported for all databases, as you can see in the walker itself:

public function walkSelectStatement(SelectStatement $AST)
{
    if ($this->platformSupportsRowNumber()) {
        return $this->walkSelectStatementWithRowNumber($AST);
    }
    return $this->walkSelectStatementWithoutRowNumber($AST);
}

private function platformSupportsRowNumber()
{
    return $this->platform instanceof PostgreSqlPlatform
        || $this->platform instanceof SQLServerPlatform
        || $this->platform instanceof OraclePlatform
        || $this->platform instanceof SQLAnywherePlatform
        || $this->platform instanceof DB2Platform
        || (method_exists($this->platform, 'supportsRowNumberFunction')
            && $this->platform->supportsRowNumberFunction());
}

As last chance, you could 'add' indexes by yourself, even with the paginator.

If you know the page size and the current page, you can calculate the rank:

$pageSize = 2;
$currentPage = 1;
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('t.score') // Here I use only the t.score, but you could put the whole class
        ->from(MainEntity::class, 'u')
        ->leftJoin('u.third_table', 't')
        ->orderBy('t.score', 'DESC');
$queryBuilder->setMaxResults($pageSize);
$queryBuilder->setFirstResult(($currentPage - 1) * $pageSize);

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($queryBuilder->getQuery());
$adapter = new \DoctrineORMModule\Paginator\Adapter\DoctrinePaginator($paginator);

$results = [];
$currentItem = 1 + ($currentPage - 1) * $pageSize;
foreach($adapter->getPaginator()->getIterator()->getArrayCopy() as $result){
    $results[] = [
        'rank' => $currentItem++,
        'item' => $result
    ];
}
var_dump($results);

Result:

$pageSize = 2;                                        $pageSize = 2;
$currentPage = 1;                                     $currentPage = 2;

Array                                                 Array
(                                                     (
    [0] => Array                                          [0] => Array
        (                                                     (
            [rank] => 1                                           [rank] => 3
            [item] => Array                                       [item] => Array
                (                                                     (
                    [score] => 200                                        [score] => 8
                )                                                     )

        )                                                     )

    [1] => Array                                          [1] => Array
        (                                                     (
            [rank] => 2                                           [rank] => 4
            [item] => Array                                       [item] => Array
                (                                                     (
                    [score] => 10                                         [score] => 6
                )                                                     )

        )                                                     )

)                                                     )