如何在CakePHP查询中添加SQL RANK()函数并去除多余括号

问题背景 #

我在ClassementsTable类中有一段未包含排名的CakePHP查询代码：

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

需要添加如下SQL的RANK()子句生成cl_rank字段：

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

尝试使用newExpr方法实现时，生成的SQL语句中RANK() over部分被自动添加了多余括号，导致SQL不正确，需要去除这些括号。

解决方案 #

不要直接用字符串形式传递窗口函数表达式，而是使用CakePHP提供的func()方法来构造RANK()窗口函数，框架会自动生成正确的无多余括号的SQL：

// 构造RANK()窗口函数表达式
$rankExpr = $this->func()->rank()->over([
    'order' => ['points' => 'DESC']
]);

$classement = $this->find('all')
    ->contain(['Users' => ['fields' => ['id', 'user_login', 'picture']]])
    ->select([
        'classement', 
        'points', 
        'participations',
        'cl_rank' => $rankExpr // 添加排名字段
    ])
    ->order(["Classements.points" => "desc", "Classements.participations" => "asc"])
    ->disableHydration();

如果需要更复杂的窗口规则（比如按字段分区排名），可以在over()方法中添加partition参数，例如：

$rankExpr = $this->func()->rank()->over([
    'partition' => 'category_id',
    'order' => ['points' => 'DESC']
]);

原理说明 #

使用func()->rank()是CakePHP ORM对SQL函数的原生支持，结合over()方法可以正确构造窗口函数语法，避免手动拼接字符串时被框架自动添加多余括号的问题。这种方式也能更好地适配不同数据库的语法差异，保证查询的兼容性。

内容的提问来源于stack exchange，提问作者dype