CakePHP クエリービルダー活用

CakePHP 3系を利用したQueryBuilderの活用法
複雑なSQL(Group BYを主体)を記載する方法を記載

基本的に、特別なケースでない場合初期化の処理は
省きController上で動作させる前提で記載します

Software	Version
Postgres	9.6.1
CakePHP	3.3.16

まずはテーブルとデータを作成.
SQL

CREATE TABLE users (
 user_id SERIAL,
 user_name VARCHAR(256),
 role VARCHAR(32) DEFAULT 'author',
 created_date TIMESTAMP DEFAULT NOW(),
 update_date TIMESTAMP DEFAULT NOW(),
 PRIMARY KEY(user_id)
);

CREATE TABLE products (
 product_id SERIAL,
 product_name VARCHAR(256),
 price INTEGER,
 PRIMARY KEY(product_id)
);

CREATE TABLE product_orders (
 product_order_id SERIAL,
 product_id INTEGER,
 date TIMESAMP,
 user_id INTEGER,
 PRIMARY KEY(product_order_id)
);

データ用SQL

INSERT INTO users(user_name) VALUES
('User A'),
('User B');

INSERT INTO products(product_name, price) VALUES
('product A', 1000),
('product B', 100),
('product C', 200);

INSERT INTO product_orders(product_id, date, user_id) VALUES
(1, '2017/06/24 15:00:00', 1),
(1, '2017/06/24 16:00:00', 2),
(2, '2017/06/24 17:00:00', 3),
(1, '2017/06/25 15:00:00', 2),
(2, '2017/06/25 16:00:00', 3),
(2, '2017/06/26 15:00:00', 1);

基本的な共通処理

use Cake\ORM\TableRegistry;

public function initialize(array $config) {
    $this->Users = TableRegistry::get('Users');
    $this->Products = TableRegistry::get('Products');
    $this->ProductOrders = TableRegistry::get('ProductOrders');
}

1. 簡単なGroup BY

商品IDと合計件数を抽出

SQL

SELECT product_id, COUNT(product_order_id) AS total FROM product_orders GROUP BY product_id;

PHP

$productOrders = $this->ProductOrders->find();
$datas = $productOrders->select([
    'product_id',
    'total' => $productOrders->func()->count('product_order_id')
])->toArray();

1. 少し複雑なGroup By

SELECT
    product_id, product_name, T1.total
FROM
    products
INNER JOIN (
    SELECT 
        product_id, COUNT(product_order_id) AS total
    FROM
        product_orders
    WHERE
        date >= '2016/06/25'
    GROUP BY product_order_id
) AS T1 USING(product_id)

PHP

$products = $this->Products->find();
$productOrders = $this->ProductOrders->find();
$datas = $productOrders->select([
    'product_id',
    'total' => $productOrders->func()->count('product_order_id')->where(['date' => new Time('2017-06-25')])
]);

$productsRecored = $products->join([
    'T1' => [
        'table' => $datas,
        'type' => 'INNER',
        'conditions' => 'T1.product_id = Products.product_id',
    ]
])->select([
    "product_id",
    "product_name",
    "total" => "T1.total"
])->toArray();

自分が困ったSQLを記載