アソシエーションで集約関数を使いたい

CakePHP 1.2(6598)。 PostgreSQL 8.1

はまった。親テーブルに対して、子テーブルを集約関数(COUNT,SUM など)でサマった結果を結合したい場合です。

例えば、 User hasMany Post という関係について User hasOne (user_id 毎のPost 数) と、いうアソシエーションを設定できないか。理想は、

    Users
LEFT JOIN
 (SELECT user_id, COUNT(id) AS cnt FROM Posts GROUP BY user_id) postcounts
ON
 Users.id = postcounts.user_id

こんな問合せが組立てられること。

• retrieving aggregated data from the model
• Adding an extra column (sub query) to a model using associations

conditions に GROUP BY を記述する方法は、使えませんでした。GROUP BY が結合条件(ON 節)に入ってしまって、 syntax error です。

とりあえず、2点考えてみましたが、何れも搦め手ですっきりしません。

1. データベースビューを使う

PostgreSQL なのでデータベースビューを作成することができます。

CREATE OR REPLACE VIEW postcounts AS
SELECT user_id, COUNT(id) AS cnt
FROM Posts
GROUP BY user_id

CakePHP でも、テーブルと同様に扱えるようです。データベースビュー (postcounts) に対する model (Postcount) を bake することができました。あとは、通常の model 同様、hasOne に設定します。

長所

• hasOne アソシエーションなので model->recursive = 0 での問合せに含まれます。検索条件に使えるので PaginatorHelper を使いやすいです。

短所

• 集約する条件を動的に指定できない。「ある期間内の投稿数」のような条件でサマることは、よくあるんじゃないかな。

2. hasMany の結果をカウントする

hasOne アソシエーションではないのですが、User hasMany Post が設定されているので、model->recursive = 1 で問合せて count(kekka['Post']) で件数は得ることができます。大量の問合せが発行されることを危惧していたのですが、WHERE 節に IN 句が使用された1文が発行されていました。

長所

• hasMany の conditions に指定することで集約条件を動的に設定できる。

短所

• 別の文なので、検索条件に含めることはできない。
• 親テーブルの件数が多い場合には、IN 句が大量となり遅くなる(と、思う)。

CakePHP 的には、hasMany をカウントの方が素直でしょうか。勝手に思っていることですが、model->query を使うと、負けてしまった気分になると思います(^_^;