例えば、製品売上の集計結果をランキング表示したいといった場合、CakePHP2ではどのようにすればよいのでしょうか。業務系の帳票処理なんかでは、しばしば複数テーブルを結合・集計するといったやや複雑なクエリが記述されます。ここでは、CakePHP2でこういった処理をする方法を記したいと思います。
単純にモデルのアソシエーションを使用して抽出したデータを、PHP側でグルグル回して集計するなんてことはしたくないでしょうね。かといって、Model::queryでやってしまうのもどうかと思いますし、せっかくなのでページネーション機能もそのまま使いたいですよね。そこで一例。
次のようなテーブルがあるとして、製品(products)の売上を集計し、金額の多寡でランキング表示します。
products
CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(200) DEFAULT NULL, `unit_price` int(11) DEFAULT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`) )
sales
CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) DEFAULT NULL, `amount` int(11) DEFAULT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`) )
ビューでは順位(rank)・製品名(product_name)・単価(unit_price)・個数(sales_amount)・金額(sales_price)を一覧表示するものとして省略し、コントローラのみ示します。
class ProductsController extends AppController { /* 中略 */ public function ranking() { $this->Product->recursive = -1; $this->Product->virtualFields = [ 'rank' => '`Sale`.`rank`', 'sales_amount' => '`Sale`.`amount`', 'sales_price' => '`Sale`.`price`' ]; $this->paginate = [ 'Product' => [ 'fields' => [ 'Product.rank', 'Product.product_name', 'Product.unit_price', 'Product.sales_amount', 'Product.sales_price' ], 'joins' => [ [ 'type' => 'LEFT', 'table' => "(SELECT a.*,COUNT(b.id)+1 AS rank ". "FROM (SELECT p.id,SUM(s.amount) AS amount,SUM(p.unit_price*s.amount) AS price FROM products AS p LEFT JOIN sales AS s ON p.id=s.product_id GROUP BY p.id) AS a LEFT JOIN (SELECT p.id,SUM(p.unit_price*s.amount) AS price FROM products AS p LEFT JOIN sales AS s ON p.id=s.product_id GROUP BY p.id) AS b ON a.price<b.price GROUP BY a.id)", 'alias' => 'Sale', 'conditions' => '`Product`.`id`=`Sale`.`id`' ] ], 'order' => 'Product.rank ASC' ] ]; $rows = $this->paginate('Product'); $this->set('rows', $rows); } /* 中略 */ }
こんな感じで記述できます。複雑なクエリ部分はjoinsのtable指定内でサブクエリとして記述してしまえば良いのです。ちなみにこの例のtable指定を見てください。これはランキングを求めるクエリです。MySQLにはRANK()関数が無いので、このような記述が必要になります。