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 = 'instagram' then 1 else null end) as instagram,
count(case when T.type = 'device' then 1 else null end) as device
from (SELECT users.username, pictures.size, pictures.type from users left join pictures on pictures.user_id = users.id) as T 
group by T.username

Associated to each 'user' there are several 'picture'. Basically, what I want to do is:

1. Left join users and pictures table
2. count pictures by their type
3. sum up picture sizes
4. group by user

The SQL query works when applied to the database, but I need to represent this within my CakePHP 3.0 application. I managed to LEFT JOIN tables using model associations. In UsersTable:

$this->hasMany('Pictures', [
'foreignKey' => 'user_id',
'joinType' => 'LEFT'
]);

Then in my find method:

$options['contain'] = ['Pictures' => function ($q) {
return $q
->select(['user_id', 'size', 'type']);}];
$options['fields'] = array('Users.username');
$query = $this->find('all', $options);

How to continue the query? If I try to access to contained data (as with 'Pictures.type') I'm returned with SQL error stating that the column isn't in the field list. I feel this might be the wrong approach, but I can't think of other ways.

Thanks in advance!

回答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;