Cakephp 3 multiple condition in contain

Question

I want to get my Jobs with associated Jobstasks, but from Jobstasks i want to get counted total1 - all tasks in job (thats works well), and (commented, ofcourse not working) total2 - counted finished tasks. Any idea?

$jobs = $this->Jobs->find('all')
            ->where(['Jobs.user_id' => $user_id])
            ->andWhere(['Jobs.start >' => new \DateTime('+6 days')])
            ->contain(['Jobgroups', 
                'Jobstasks' => function($q){
                    $q->select([
                        'JobsTasks.job_id',
                        'JobsTasks.finished',
                        'total1' => $q->func()->count('JobsTasks.job_id'), 
                        //'total2' => $q->func()->where(['JobsTasks.finished' => true])->count()
                    ])->group(['JobsTasks.id']);
                    return $q;
                }

            ])
            ->order(['Jobs.start' => 'ASC']);

Answer 1

Thnx to you Arilia, your total2 works great :) change total1 is not necessary. Below is solution. And i have to change Grouping for correct data output.

$jobs = $this->Jobs->find('all')
            ->where(['Jobs.user_id' => $user_id])
            ->andWhere(['Jobs.start >' => new \DateTime('+6 days')])
            ->contain(['Jobgroups', 
                'Jobstasks' => function($q){
                    $q->select([
                        'JobsTasks.job_id',
                        'total1' => $q->func()->count('JobsTasks.finished'), 
                        'total2' => "SUM(IF(JobsTasks.finished = 1, 1, 0))" 
                    ])->group(['JobsTasks.job_id']);
                    return $q;
                }
            ])
            ->order(['Jobs.start' => 'ASC']);

Answer 2

I guess that you can simply SUM the finished column, sinc it is 1 for the finished tasks and 0 otherwise

'total1' => $q->func()->sum('JobsTasks.finished')

This works for booleans. In case you have a more complex condition I would create a calculated field evaluating your condition instead of a WHERE clause

something like

'total2' => "SUM(IF(JobsTasks.finished = true, 1, 0))"