アシアルブログ

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

DoctrineでデータベースのViewを使って楽をする

PHPも好きですが本当はSQLも結構好きな岡本です。
アシアルに入社する前はネットショップ・システムに携わっており、
SQLでガリガリと売り上げや利用統計などを集計していました。
しかし、symfonyを使った開発ではデータベースに対する操作は基本的に、
ORマッパー(PropelやDoctrine)を介して行うため、SQLの強力な集計機能が恋しくなる時があります。

フォームから受け取ったデータをデータベースに保存するような更新系の処理は
insert文を書くよりもDoctrineでsaveメソッドを発行した方が楽だと思うのですが、
参照系の処理では集計やサブクエリを活用したselect文に比べると表現力に物足りなさを感じてしまいます。

「Viewに対してDQLが使えれば楽なのに」

と思っていたら、実は問題なく使えたのでやり方をお伝えします。

使い方は簡単で、データベース上にViewを作った後に


$ vi config/doctrine/schema.yml


へ実表と同じように設定ファイルを書き


$ symfony doctrine-build-model


を実行すればDoctrineのモデルが作成されます。

Viewですので更新処理は行えませんが、
参照系のカスタムメソッドは追加できます。

■サンプル


※クリックで拡大

ポイントの概念も売れた日付の記録すらない簡単な注文テーブル群でカッコ悪いのですが、
これを元にViewを作成してschema.ymlを書いてみます。

まずViewは名前と住所、注文商品数と合計金額そして粗利益を集計し
以下の様な表を作成します。



Viewを作成するSQLは以下のようになります。


CREATE VIEW  cart_order AS SELECT 
  cart.id ,
  member.name ,
  member.address ,
  (SELECT COUNT(sale.id) FROM sale WHERE cart_id = cart.id) AS sales_amount,
  (SELECT SUM(item.price) FROM sale LEFT JOIN item ON sale.item_id = item.id WHERE cart_id = cart.id) AS sales_price,
  (SELECT SUM(item.price) - SUM(item.cost) FROM sale LEFT JOIN item ON sale.item_id = item.id WHERE cart_id = cart.id) AS sales_margin
FROM
cart 
  LEFT JOIN member ON cart.member_id = member.id;

※データベースはMysqlを利用

このViewに対するschema.ymlは以下のようになります。


CartOrder: 
  tableName: cart_order
  columns: 
    id: 
      type: 
      size: 
      notnull: true
      primary: true
    name: 
      type: string
      size: 4000
    address: 
      type: string
      size: 4000
    sales_amount: 
      type: 
      size: 
    sales_price: 
      type: 
      size: 
    sales_margin: 
      type: 
      size: 


 DoctrineのようなORマッパーを使った開発でも、
Viewを使って予めテーブルを連結・集計しておけば情報の取得で楽が出来ます。
また、取得するカラムや行をViewで絞り込んでおけばセキュリティ的な面も固め易いと思います。
 また、Viewは便利ですが沢山作ると管理が大変になるため、
細かい条件の絞込みや表示の修正はDBモデルのカスタムメソッドで行うと、
管理もしやすく更に楽が出来ます。