cakephp 如何使用sql rank函数编写sql请求(已更新)

如何将此sql请求（cl_rank）添加到我的请求中？
我的请求（在类ClassementsTable中）没有等级：

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

我需要如下所示的cl_rank：

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

newExpr可能是一个解决方案：

$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();

但是在（order by points desc）cl_rank上的RANK（）周围添加了一些额外的（），并且sql请求不正确。
生成的sql请求：

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

这个可以（不带（））：

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

有没有办法去掉（）？
谢谢你的帮助

窗口函数是开箱即用的（从4.1.0开始），可以通过函数生成器创建。
有些窗口函数有简写形式，例如lag()、lead()和rowNumber()（可能是rank()，其他函数也应该在其中），而其他函数，基本上任何窗口函数都可以通过aggregate()创建：

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

通过显式调用over()，可以将任何聚合函数转换为窗口函数（当不添加其它子句时，其将创建空的OVER ()子句），或者通过调用partition()、order()、range()、rows()、groups()、frame()、excludeCurrent()excludeGroup()或excludeTies()，它们将隐式调用over()。
另请参阅

*Cookbok〉数据库访问和ORM〉查询生成器〉窗口函数

cakephp 如何使用sql rank函数编写sql请求(已更新)

1条答案

相关问题

热门标签

最新问答