将SQL查询翻译为CakePHP 3.0 [英] Translate SQL query to CakePHP 3.0

问题描述

查询如下

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. 左连接用户和图片表格


2. 按照类型计算图片


3. 总计图片大小


4. 按用户分组

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

SQL查询在应用于数据库时工作，但我需要在我的CakePHP 3.0应用程序中表示这一点。
我使用模型关联管理到LEFT JOIN表。在UsersTable中：

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

然后在我的find方法中：

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

如何继续查询？如果我尝试访问包含的数据（与'Pictures.type'）我返回与SQL错误，说明该列不在字段列表中。
我觉得这可能是错误的方法，但我不能想出其他方法。

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.

提前感谢！

推荐答案

已解决

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

这篇关于将SQL查询翻译为CakePHP 3.0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！