アシアルブログ

アシアルの中の人が技術と想いのたけをつづるブログです

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で開発するときは、是非こういった点を活用していきましょう。