DoctrineのSELECT句と集計関数の扱い方
こんにちは。小川です。
今回はDoctrineのSELECT句と集計関数の扱い方などについて書いていこうと思います。
始める前に今回使うデータベースのスキーマとサンプルとなるフィクスチャデータを書いておきます。一応symfony 1.2上で使うことを前提としています。
# config/doctrine/schema.yml
User:
actAs:
Timestampable:
columns:
id:
type: integer(4)
notnull: true
primary: true
autoincrement: true
name:
type: string(255)
notnull: true
Account:
actAs:
Timestampable:
columns:
id:
type: integer(4)
notnull: true
primary: true
autoincrement: true
user_id:
type: integer(4)
notnull: true
amount:
type: integer(4)
notnull: true
relations:
User:
foreignAlias: Accounts
# data/fixtures/fixtures.yml
User:
katsuhiro:
name: Katsuhiro Ogawa
massie:
name: Masahiro Tanaka
Account:
Acount_1:
amount: 1500
User: katsuhiro
Acount_2:
amount: 3000
User: katsuhiro
Acount_3:
amount: 4000
User: massie
Acount_4:
amount: 10000
User: massie
Acount_5:
amount: 1500
User: katsuhiro
上記を用いて symfony doctrine:build-all-reload コマンドを実行すればデータは整います。
Userはユーザ、Accountは売上のモデルとなります。今回はこの2つのモデルを使って説明していきます。
では話を進めて参りましょう。集計関数というのはSUMやMAX、AVGといったSQL上で集計を行う関数のことです。
SELECT u.name, SUM(a.amount) FROM user u LEFT JOIN account a ON u.id = a.user_id GROUP BY u.name;
上記はユーザごとの販売金額の合計を集計するSQLの例です。
このSQLを単純にDoctrine_Queryにすると以下のようになります。
<?php
$totalAmountList = Doctrine_Query::create()->select('u.name, SUM(amount)')
->from('User u, u.Accounts a')->groupBy('u.name')->execute();
さて、$totalAmountListからSUM(amount)を取得したい場合はどう指定すればよいでしょうか。
実際に上記のDoctrine_Queryが取得する内容をみてみましょう。
$totalAmountListをtoArrayなどしてダンプしてもいいですが、単純なDQLの結果を取得するだけであればコマンドラインで確認するのが楽かと思います。
実はsymfonyにはDQLの結果を取得するコマンドが用意されています。早速使ってみてみましょう。
$ symfony doctrine:dql "SELECT u.name, SUM(a.amount) FROM User u, u.Accounts a GROUP BY u.name"
>> doctrine executing dql query
DQL: SELECT u.name, SUM(a.amount) FROM User u, u.Accounts a GROUP BY u.name
found 2 results
-
id: '1'
name: 'Katsuhiro Ogawa'
SUM: '6000'
Accounts:
-
SUM: '6000'
-
id: '2'
name: 'Masahiro Tanaka'
SUM: '14000'
Accounts:
-
SUM: '14000'
「SUM」と表示されているのがSUM(a.amount)の部分になります。括弧以降の部分が表示され ていない状態です。
実際に$totalAmountListから取得する場合、この状態であれば $totalAmountList->SUM とやることで取得できます。
また、連結している全てのオブジェクトに集計部分がついています。エイリアスで制御できないからでしょうね。
上記のように、集計関数はDoctrineでも特に意識せずに使用することができます。ただ、SUMという名前になってしまうのは正直使いづらいですよね。
そこで登場するのがSQLのAS句です。これをSELECT句内で指定すると、
$ symfony doctrine:dql "SELECT u.name, SUM(a.amount) AS total_amount FROM User u, u.Accounts a GROUP BY u.name"
>> doctrine executing dql query
DQL: SELECT u.name, SUM(a.amount) total_amount FROM User u, u.Accounts a GROUP BY u.name
found 2 results
-
id: '1'
name: 'Katsuhiro Ogawa'
total_amount: '6000'
Accounts:
-
total_amount: '6000'
-
id: '2'
name: 'Masahiro Tanaka'
total_amount: '14000'
Accounts:
-
total_amount: '14000'
このようにきちんと変換され、 $totalAmountList->total_amount として取得できるようになります。
もちろんAS句は全ての値に使用できます。また、AS自体は省略しても問題なく動作します。
DATE関数でグループ化したいといった場合も、 DATE(created_at) AS create_date のようにしてあげます。
また全ての項目をそのまま取得したい場合は、u.*のようにエイリアスとアスタリスクで指定します。
Doctrineはこのように、集計関数などを用いたクエリでも柔軟に対応してくれます。
Doctrineで開発するときは、是非こういった点を活用していきましょう。
