自分で書いた SQL から populated Object を作る

Symfony の採用する Propel O/R マッパは非常に便利で強力ですが、複雑にネストした query など、効果が発揮できない場面はあります。あるいはそれから生成される SQL の効率が悪いとか。そんなことにいちいち気を遣う僕の頭がおかしいのかもしれないけれど、SQL を直に書いていた頃に出来た事が出来ないのは、Framework を使う理由の本末転倒だなとは感じていました。僕が知らないだけなのかもしれない。ここにある Criteria Object の Reference にある事以上をうまく解説してるサイトとかあったら誰か教えてください。

という訳で、生の SQL Query を Symfony 標準の Propel+Creole を使って使う方法をまとめてみたいと思います。Askeet Tutorial はやや古くなりつつありますが、First Reference として引いておきましょう。

symfony Advent Calendar: symfony advent calendar day thirteen: Tags

symfony アドベントカレンダー 13日目: タグ

僕が最初によくわかってなかったのは、O/R マッパがあるのに生の SQL を使うのはなんだか負けた気がする、と云うか、O/R マッパを使えば、もう SQL を使わなくていいのかと思ってた。まともに考えて、SQL を Object に抽象化する、って相当難しそうだし、めんどくさいものだと。でもそれは偏見で、O/R マッパって出来ることは凄い単純で出来ることも限られてる。そのかわり理解しやすい、自動化しやすい、他のソフトウェアから扱いやすい、ということかしらん。

多分肝心なのは、既に SQL を知ってる人は、DB とお話しするのにオブジェクト O/R マッパを使う必要は必ずしも無くて、パファーマンスが必要なときとか、SQL が得意なことをする時は生の SQL を使うべき、だと云うこと。難しくないし、そんなにめんどくさくもない、ということ今回のエントリで解説してみたい。

というわけでやってみましょう。ここでは何か target に tag を付けて、分類したいとします。tag を正規化すると target と tag は m:n な関係になりますね。schema.yml は以下。

propel:
  _attributes:      {defaultIdMethod: native, noxsd: true}


  target:
    _attributes:          { phpName: Target }
    id:
      type:               integer
      required:           true
      primaryKey:         true
      autoIncrement:      true
    content:
      type:               varchar(64)
      description:        "Target content"


  tag:
    _attributes:          { phpName: Tag }
    id:
      type:               integer
      required:           true
      primaryKey:         true
      autoIncrement:      true
    name:
      type:               varchar(64)
      description:        "Tag name"


  target_tag_joint:
    _attributes:          { phpName: TargetTagJoint }
    pkey:
      type:               integer
      required:           true
      primaryKey:         true
      autoIncrement:      true
    tag_id:
      type:               integer
      foreignTable:       tag
      foreignReference:   id
    target_id:
      type:               integer
      foreignTable:       target
      foreignReference:   id

Fixture として Fortune 500 をネタにしましょう。fixtures/data.yml は最後に付けておきます。実際あんまりいい例ではなかったか…

こんな時に、例えば一つの tag から target object の集合が欲しくて、普通に Criteria を使うと…

$tag_name = 'car';


$c = new Criteria();
$c->add(self::NAME, $tag_name);
$tag_obj = TagPeer::doSelectOne($c);


$c = new Criteria();
$c->add(self::TAG_ID, $tag_obj->getId());
$arr = self::doSelect($c);


$result = array();
foreach($arr as $joint)
{
  $c = new Criteria();
  $c->add(TargetPeer::ID, $joint->getTargetId());
  $result[] = TargetPeer::doSelectOne($c);
}


return $result;

こんな感じでしょうか。前述の fixture くらいの小さな集合だったら、全く問題ないレヴェルのクエリですが、もし、例えば、一つの tag から 100 の target が引けるとすると、ちょっとシンドイでしょうね。なにせ 100 回クエリが投げられる訳ですから。

SQL 言語を理解している人には当たり前過ぎかもしれませんが、この問題は以下のような入れ子になった INNER JOIN を使うと、1 回の SQL クエリで同じ集合を得ることが出来ます。（IN 述語を使う方がいいって？後述します）

SELECT 
  TAR.id, TAR.content 
FROM 
  target AS TAR 
  INNER JOIN (
    SELECT 
      TTJ.target_id 
    FROM 
      target_tag_joint AS TTJ 
      INNER JOIN ( 
        SELECT 
         TAG.id 
        FROM 
          tag AS TAG 
        WHERE 
          TAG.name = ? 
      ) AS T ON TTJ.tag_id = T.id 
  ) AS RES ON TAR.id = RES.target_id ; 

（Join を使った query は禁止とか、アクセスの桁があがると Join は遅いからこういう正規化はダメ、というのは別の話題、ということで。）

このクエリを実行すると、ResultSet オブジェクトになり、例えば配列として結果集合を取得できます。ただ他の部分との整合性として、特別にこの method だけは配列としてアクセス、というのもすこしおかしいし難しい。どうせなら、Criteria を使うのと同じく O/R マッピングをしたい。populate してみます。

$con    = Propel::getConnection();
$stmt   = $con->prepareStatement($query);
$stmt->setString(1, $_tag);
$rs     = $stmt->executeQuery(null, ResultSet::FETCHMODE_NUM);


$result = array();
$cl     = Propel::import(TargetPeer::getOMClass());


while ($rs->next())
{
  $obj = new $cl();
  $obj->hydrate($rs, 1);
  $result[] = $obj;
}


return $result;

これは、もうまるまま Base***Peer の populateObjects メソッドのコピーです。hydrate の第二引数の int は、Base*** の hydrate のソースを読めばわかりますが、ResultSet の index を 1 から降り直したもの…訳が分かりませんね。ソースを追ってみましょう。以下、symfony propel-build-model で生成された BaseTarget.php から引用します。

public function hydrate(ResultSet $rs, $startcol = 1)
{
  try 
  {
    $this->id      = $rs->getInt($startcol + 0);
    $this->content = $rs->getString($startcol + 1);
    $this->resetModified();
    $this->setNew(false);
    return $startcol + 2;
  } 
  catch (Exception $e) 
  {
    throw new PropelException("Error populating Target object", $e);
  }
}

propel-build-model した時に、schema.yml をパースして、ResultSet を指数配列で受け取るようにして（ResultSet::FETCHMODE_NUM）順番を降っているだけだったのですね。だからこれをうまく使えば一回のクエリで２つのオブジェクトを受け取る事も可能なわけです。

まとめ

Query さえ書ければ、populate の部分はほとんど写経でも動くはずです。大事な点は、Prepared Statement を使って、ちゃんと値を代入する事と、executeQuery(null, ResultSet::FETCHMODE_NUM) のようにして指数配列で ResultSet を受け取る事です。

Hydrate も仕組みを理解すれば便利に使えます。今回の例でいえば、Target のオブジェクトに 1:n な関係の値が入る場合（例えば各 Target 項目に複数毎写真を付けられる、とか）、一つの row に target と picture が一緒にかえるような SQL を書いて、一気に hydrate する、とか。

捕捉: IN 述語

今回僕が挙げたクエリの INNER JOIN を IN を使って書き直すと、こんな感じに。

SELECT 
  TAR.id, TAR.content 
FROM 
  target AS TAR 
WHERE 
  TAR.id 
  IN ( 
    SELECT 
      TTJ.target_id 
    FROM 
      target_tag_joint AS TTJ 
    WHERE 
      TTJ.tag_id 
      IN (
        SELECT 
          TAG.id 
        FROM 
          tag AS TAG 
        WHERE 
          TAG.name = ? 
      )
  ) ; 

先に INNER JOIN を使ったのは…僕がそれに馴れているからで深い意味はないんですが、書いてみて、一緒に走らせてみると、項目が多くなれば INNER JOIN を使ったクエリの方が、メモリは食いますがほんのちょびっと速くなる感じでした。

Symfony snippets: Sub-selects using Propel というページに IN 述語を使ったサブクエリを Criteria オブジェクトを使って投げる、という例があります。どっちにしても、Criteria で今回のような 2 段 nest をやる方法はわかりませんし、主題としては Populate Object は難しくないYO! という事なのでよしとします。

続きに fixtures/data.yml を挙げておきます。

Fixture

Target:
  tar0:
    content:  "Wal-Mart"
  tar1:
    content:  "Exxon Mobil"
  tar2:
    content:  "General Motors"
  tar3:
    content:  "Chevron"
  tar4:
    content:  "ConocoPhillips"
  tar5:
    content:  "General Electric"
  tar6:
    content:  "Ford Motor"
  tar7:
    content:  "Citigroup"
  tar8:
    content:  "Bank of America"
  tar9:
    content:  "American International Group"


Tag:
  tag0:
    name:     "retail"
  tag1:
    name:     "oil"
  tag2:
    name:     "car"
  tag3:
    name:     "aircraft"
  tag4:
    name:     "electricity"
  tag5:
    name:     "finance"
  tag6:
    name:     "insurance"


TargetTagJoint:
  ttj0:
    target_id:  tar0
    tag_id:     tag0
  ttj1:
    target_id:  tar1
    tag_id:     tag1
  ttj2:
    target_id:  tar2
    tag_id:     tag2
  ttj3:
    target_id:  tar3
    tag_id:     tag1
  ttj4:
    target_id:  tar4
    tag_id:     tag1
  ttj5:
    target_id:  tar5
    tag_id:     tag3
  ttj6:
    target_id:  tar5
    tag_id:     tag4
  ttj7:
    target_id:  tar5
    tag_id:     tag5
  ttj8:
    target_id:  tar6
    tag_id:     tag2
  ttj9:
    target_id:  tar7
    tag_id:     tag5
  ttj10:
    target_id:  tar8
    tag_id:     tag5
  ttj11:
    target_id:  tar9
    tag_id:     tag6