■テーブル構成(テーブル名:フィールド)
users:id,name
orders:id,user_id
■SQL文で書く場合
SELECT users.id,users.name,order_num FROM `users` LEFT JOIN
(SELECT COUNT(user_id) AS order_num,user_id FROM `order` GROUP BY user_id) AS order_tb
ON users.id=order_tb.user_id
CakePHPは1.3.2を使っています。情報が足りない場合はコメントにて指摘してください。
コメントに書いたようなbuidStatementを使って、次のURLのようなLEFT JOINの方法を参考にすると、次のように書けます。
$dbo = $this->User->getDataSource(); $subQuery = $dbo->buildStatement(array('fields' => array("user_id", "COUNT(id) AS order_num"), 'table' => $dbo->fullTableName($this->Order), 'alias' => 'Order', 'limit' => null, 'offset' => null, 'joins' => array(), 'conditions' => array(), 'order' => null, 'group' => "user_id"), $this->Order); $options = array(); $options['conditions'] = array(); $options['fields'] = array("User.id", "User.name", "order_tb.order_num"); $options['joins'] = array(); $options['joins'][] = array("type" => 'LEFT', "alias" => 'order_tb', "table" => "({$subQuery})", "conditions" => 'User.id = order_tb.user_id'); $this->User->find('all', $options);
この結果
SELECT `User`.`id`, `User`.`name`, `order_tb`.`order_num` FROM `users` AS `User` LEFT JOIN (SELECT user_id, COUNT(id) AS order_num FROM `orders` AS `Order` WHERE 1 = 1 GROUP BY user_id ) AS `order_tb` ON (`User`.`id` = `order_tb`.`user_id`) WHERE 1 = 1
というクエリが実行されます。
ありがとうございます。求める結果が得られました。