什么是CakePHP控制器中的Cake SQL？及指定SQL的使用方法

CakePHP中的「Cake SQL」一般指框架原生的ORM查询构建器——它允许你用面向对象的语法编写数据库查询，无需直接写原生SQL字符串。这种方式能自动适配不同数据库类型、预防SQL注入，同时让代码更易读和维护。当然如果遇到极复杂的查询，也可以在控制器中直接执行原生SQL，但优先推荐使用ORM查询构建器。

下面提供两种实现方案，优先推荐ORM查询构建器方案：

方案1：使用ORM查询构建器（推荐） #

假设你的模型关联已正确配置（Evaluation模型关联EvaluationUserRatings和EvaluationsUsersAndTeam），控制器中代码如下：

// 获取Evaluation表实例
$evaluationTable = $this->getTableLocator()->get('Evaluation');

// 构建子查询：统计已提交评分的玩家数
$playerRatingGivenSubquery = $evaluationTable->find()
    ->select(['count' => $evaluationTable->find()->func()->count('id')])
    ->innerJoinWith('EvaluationUserRatings', fn($q) => $q->where(['EvaluationUserRatings.evaluation_id = Evaluation.id']))
    ->limit(1);

// 构建子查询：统计参与的玩家总数（修正原SQL中拼写错误的evalution_id为evaluation_id）
$playerCountSubquery = $evaluationTable->find()
    ->select(['count' => $evaluationTable->find()->func()->count('id')])
    ->innerJoinWith('EvaluationsUsersAndTeam', fn($q) => $q->where(['EvaluationsUsersAndTeam.evaluation_id = Evaluation.id']))
    ->limit(1);

// 构建子查询：获取提交评分的用户ID（注意：若有多条结果需加LIMIT 1或用聚合函数处理）
$puserIdSubquery = $evaluationTable->find()
    ->select(['rating_added_by'])
    ->innerJoinWith('EvaluationUserRatings', fn($q) => $q->where(['EvaluationUserRatings.evaluation_id = Evaluation.id']))
    ->limit(1);

// 主查询
$results = $evaluationTable->find()
    ->select([
        'current_time' => $evaluationTable->find()->func()->time($evaluationTable->find()->func()->now()),
        'evaluation_start_time',
        'temp_start_time' => $evaluationTable->find()->func()->addTime(
            'evaluation_start_time',
            $evaluationTable->find()->func()->concat(['evaluation_hour_after' => 'literal', ':00:00' => 'literal'])
        ),
        'temp_end_time' => $evaluationTable->find()->func()->addTime(
            'evaluation_start_time',
            $evaluationTable->find()->func()->concat(['evaluation_hour_after' => 'literal', ':14:00' => 'literal'])
        ),
        'player_rating_given' => $playerRatingGivenSubquery,
        'player_count' => $playerCountSubquery,
        'puser_id' => $puserIdSubquery,
        'Evaluation.*'
    ])
    ->where(fn($exp) => $exp
        ->gte('evaluation_date', $evaluationTable->find()->func()->date($evaluationTable->find()->func()->now()))
        ->and(
            $exp->or(
                $exp->notEq('evaluation_day_after', 0),
                $exp->notEq('evaluation_hour_after', 0)
            )
        )
        ->eq('evaluation_status', '1')
    )
    ->having(fn($exp) => $exp
        ->gte('player_count', 'player_rating_given')
        ->gte($evaluationTable->find()->func()->time($evaluationTable->find()->func()->now()), 'temp_start_time')
        ->lte($evaluationTable->find()->func()->time($evaluationTable->find()->func()->now()), 'temp_end_time')
    )
    ->toArray();

方案2：直接执行原生SQL #

如果ORM查询构建器无法满足需求，可直接执行原生SQL：

// 获取表实例与数据库连接
$evaluationTable = $this->getTableLocator()->get('Evaluation');
$connection = $evaluationTable->getConnection();

// 原生SQL（修正原SQL中拼写错误的evalution_id为evaluation_id）
$sql = <<<SQL
SELECT Time(NOW()),
       evaluation_start_time,
       AddTime(evaluation_start_time, CONCAT(evaluation_hour_after, ':00:00')) temp_start_time,
       AddTime(evaluation_start_time, CONCAT(evaluation_hour_after, ':14:00')) temp_end_time,
       (SELECT count(id) FROM evaluation_user_ratings WHERE evaluation_id = evaluation.id) player_rating_given,
       (SELECT count(id) FROM evaluations_users_and_team WHERE evaluation_id = evaluation.id) player_count,
       (SELECT rating_added_by FROM evaluation_user_ratings WHERE evaluation_id = evaluation.id LIMIT 1) AS puser_id,
       evaluation.*
FROM evaluation
WHERE evaluation_date >= date(NOW())
  AND (evaluation_day_after != 0 OR evaluation_hour_after != 0)
  AND evaluation_status = '1'
HAVING player_count >= player_rating_given
AND Time(NOW()) >= temp_start_time
AND Time(NOW()) <= temp_end_time
SQL;

// 执行查询并获取结果
$results = $connection->execute($sql)->fetchAll('assoc');

注意事项 #

• 优先使用ORM查询构建器，它能自动处理表前缀、字段转义和SQL注入防护
• 原生SQL中若有动态参数，需使用占位符绑定（如$connection->execute($sql, [$param1, $param2])），避免直接拼接字符串引发注入风险
• 原SQL中puser_id的子查询若返回多条结果会报错，已添加LIMIT 1处理，若需获取所有提交用户ID可改用GROUP_CONCAT(rating_added_by)

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