mysql - cakephp 3.4 mySQL 到 orm 转换

Question

我当前的 MYSQL 查询是

SELECT
    cl.user_id,
    COUNT(q.id) as completed_questions,
    (
        SELECT
            SUM(points) 
        FROM
            completed_levels 
        WHERE
            user_id = cl.user_id 
    )
    as level_point 
FROM
    completed_levels cl 
    JOIN
        questions q 
        on q.level_id = cl.level_id 
GROUP BY
    cl.user_id;

我当前的 ORM 正在遵循我只是无法处理

中的选择部分

$completedLevels = TableRegistry::get('CompletedLevels');
$completedLevels = $completedLevels->find('All');
$completedLevels = $completedLevels
    ->contain(['Users', 'Levels'])
    ->select([
        'user_name' => 'Users.name',
        'count_questions' => 'COUNT(Questions.id)',
        'total_pints' => [
            'select'=>'SUM(CompletedLevels.points)'
        ]
    ])
    ->join([
        'table' => 'Questions',
        'conditions' => 'Questions.level_id = CompletedLevels.level_id',
    ])
    ->group(['CompletedLevels.user_id'])
    ->all();

Answer 1

找到答案它的“子查询”

    $completedLevels = TableRegistry::get('CompletedLevels');
    TableRegistry::config('cl', ['table' => 'completed_levels']);
    $cl = TableRegistry::get('cl');
    $cl = $cl->find('all');
    $cl ->select($cl->func()->sum('points'));

    $completedLevels = $completedLevels->find('All');
    $completedLevels = $completedLevels
        ->contain(['Users', 'Levels'])
        ->select([
            'user_name' => 'Users.name',
            'count_questions' => 'COUNT(Questions.id)',
            'total_pints' => $cl->where('user_id = CompletedLevels.user_id')
        ])->join([
            'table' => 'Questions',
            'conditions' => 'Questions.level_id = CompletedLevels.level_id',
        ])->group(['CompletedLevels.user_id']);