cakephpwindow-functionsquery-buildercakephp-4.x

how write a sql request with sql rank function

How can I add this sql request (cl_rank) to my request ?

My request (in class ClassementsTable) without the rank is:

$classement = $this->find('all')
            ->contain(['Users' => ['fields' => ['id', 'user_login', 'picture']]])
            ->select(['classement', 'points', 'participations'])
            ->order(["Classements.points" => "desc", "Classements.participations" => "asc"])
            ->disableHydration();

I need the cl_rank like this:

select user_id, points, 
RANK() over (order by points desc)cl_rank
from classements;

newExpr could be a solution:

$query = $this->find('all');
        $expr = $query->newExpr('RANK() over (order by points desc) cl_rank');
        $classement = $query
            ->where(['user_id IN' => $equipe])
            ->contain(['Users' => ['fields' => ['id', 'user_login', 'picture']]])
            ->select(['classement', 'points', 'participations',  $expr])
            ->order(["Classements.points" => "desc", "Classements.participations" => "asc"])
            ->disableHydration();

but some extra () are added around RANK() over (order by points desc) cl_rank and the sql request is not correct.

The generated sql request :

SELECT Classements.classement AS Classements__classement, 
Classements.points AS Classements__points, 
Classements.participations AS Classements__participations, 
(RANK() over (order by points desc)cl_rank), 
Users.id AS Users__id, 
Users.user_login AS Users__user_login, 
Users.picture AS Users__picture 
FROM classements Classements INNER JOIN users Users ON Users.id = Classements.user_id 
WHERE user_id in (SELECT DISTINCT EquipesUsers.user_id AS EquipesUsers__user_id 
FROM equipes_users EquipesUsers WHERE equipe_id = :c0) 
ORDER BY Classements.points desc, Classements.participations asc

This one is OK (without the ()):

SELECT Classements.classement AS Classements__classement,
Classements.points AS Classements__points, 
Classements.participations AS Classements__participations, 
RANK() over (order by points desc)cl_rank, 
Users.id AS Users__id, 
Users.user_login AS Users__user_login, 
Users.picture AS Users__picture 
FROM classements Classements 
INNER JOIN users Users ON Users.id = Classements.user_id 
WHERE user_id in (SELECT DISTINCT EquipesUsers.user_id AS EquipesUsers__user_id FROM equipes_users EquipesUsers WHERE equipe_id = :c0) 
ORDER BY Classements.points desc, Classements.participations asc

Any idea to remove the ()?

Solution

  • Window functions are supported out of the box (as of 4.1.0), they can be created via the functions builder.

    There's shorthands for some window functions like for example lag(), lead() and rowNumber() (maybe rank() and others should've been among them), while others, and basically any arbitrary window function can be created via aggregate():

    ->select(function (\Cake\ORM\Query $query) {
    return [
        'classement',
        'points',
        'participations',
        'cl_rank' => $query
            ->func()
            ->aggregate('RANK')
            ->order(['points' => 'DESC'])
            ->setReturnType('integer'),
    ];
})

    Any aggregate function can be turned into a window function by either explicitly calling over() (which will create an empty OVER () clause when no further clauses are added), or by calling one of partition(), order(), range(), rows(), groups(), frame(), excludeCurrent(), excludeGroup() or excludeTies(), which will implicitly call over().

    See also