CakePHPでサブクエリを使って結合し、findで出力する方法を教えてください。


■テーブル構成(テーブル名:フィールド)
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を使っています。情報が足りない場合はコメントにて指摘してください。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/07/09 18:27:14
  • 終了:2010/07/10 10:27:05

ベストアンサー

id:tdoi No.1

tdoi回答回数174ベストアンサー獲得回数752010/07/10 04:00:39

ポイント100pt

コメントに書いたような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 

というクエリが実行されます。

id:k27w

ありがとうございます。求める結果が得られました。

2010/07/10 10:26:52
  • id:tdoi
    ここに記載されているサブクエリの構築方法ではだめなのでしょうか?

    http://book.cakephp.org/view/74/Complex-Find-Conditions
  • id:k27w
    質問のSQLはordersテーブル内のusers_idの登録数を数えてusersテーブルと結合するというコードですが、URLの先を見ただけでは結合までわかりません・・。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません