Translate Sql Query To Cakephp 3.0

The query is as follows select T.username, sum(T.size) as totSize, count(*) total, count(case when T.type = 'facebook' then 1 else null end) as facebook, count(case when T.type = '

Solution 1:

solved

$options['contain'] = array('Users');
$query = $this->Pictures->find('all', $options);
$query->select(['Users.username', 'Users.plan', 'Users.id']);
$query->select(['sum' => $query->func()->sum('size'),])
      ->select(['count' => $query->func()->count('*'),])
      ->select(['facebook' => $query->func()
          ->count('case when type = \'facebook\' then 1 else null end'),])
      ->select(['instagram' => $query->func()
          ->count('case when type = \'instagram\' then 1 else null end'),])
      ->select(['device' => $query->func()
           ->count('case when type = \'device\' then 1 else null end'),])
      ->group('user_id');

return$query;

This allows to select joined fields as

$result = $query->all();
$result->user->username;