CakePHP group byとinner joinでhasManyデータの件数を検索する

例えばusersテーブルとpostsテーブルがあり、「users hasMany posts」としたとき、ユーザの投稿したメッセージ件数の一覧表など作りたいことなど、普通にあると思う。そんなとき、usersテーブルとposts テーブルをJOINして、同じuser_idを持つpostsデータをGROUP BYでまとめて、count()で件数を引っ張り出す。SQLで書くと以下のような感じ。

SELECT User.id,User.name,count(Post.user_id) AS num FROM users AS User
LEFT JOIN posts AS Post ON User.id=Post.user_id
GROUP BY User.id;

ここで、投稿を１度もしていないユーザを省略したい場合は、「LEFT JOIN」を「INNER JOIN」に変更すればよい。

さて、この自分が意図するSQLを、どのようにCakePHPを利用すれば、吐き出してくれるのだろうか?

以下のような方法で何とか解決したが、ちょっとトリッキーなやり方だな、と思う(ﾉ_-;)ﾊｱ…。

$this->User->unbindModel(array('hasMany'=>array('Post')));
$this->User->bindModel(array('belongsTo'=>array('Post'=>array(
  'className'=>'Post',
  'foreignKey'=>false,
  'conditions'=>'User.id=Post.user_id',
  'type'=>'inner',
  'fields'=>array('count(Post.user_id) as num')))));

$users=$this->User->find('all', array('group'=>'User.id'));

ここでのミソは、

• belongsToを利用して「’type’=>’inner’」を利用する
• JOINの条件は、’foreignKey’をfalseにし’conditions’に記述する
• findするとき、’group’指定する

で、望みのSQLを吐き出してくれた。typeを省略すればLEFT JOINになるし、ユーザに条件を加えられるしで、なかなか使い勝手良いかも。modelに収めて隠ぺいすれば、controllerもスッキリ爽快気分になるに違いない(^o^)。

これと、group by句でposts.user_id指定したため、今日１日つぶした… orz。

以下、参考にしたサイトの記事です。ありがとうございました。

• むしの手記「Cakephpで普通にINNER JOINする」
• Sun Limited Mt.「CakePHP findAll で INNER JOIN する方法」
• nuts and bolts of cakephp「Forcing an SQL JOIN in CakePHP」

CakePHPのバージョンは、えーと、1.2.1_8004でした。