cakephp 3.4 mySQL to orm convert

Question

my Current MYSQL query is

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;

my current ORM is following i just not able to handle select part in

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

Are you asking for how to create (and/or pass) subqueries? – ndm Jul 18 '17 at 19:22 — ndm, Jul 18 '17 at 19:22

score 0 · Answer 1 · answered Jul 19 '17 at 12:36

Found answer ITS "SUB-Query"

    $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']);

cakephp 3.4 mySQL to orm convert

1 Answers1