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モデルのカスタムメソッドで行うと、
管理もしやすく更に楽が出来ます。