Zend_Db_Selectで複数テーブルからデータを取得したいのだが…

Zend_Db_Selectで複数テーブルからデータを取得したいのだが… | Inhale n’ ExhaleZend FrameworkでWebアプリを作ることになり、久しぶりにZend Frameworkのリファレンスを見ながらコードを書いていたのだが、ものすごく単純なことなはずなのにZend_Db_Selectの仕様（？）のせいでちょいとハマっていた。

Webアプリではお決まりのユーザー管理機能なんだが、話を単純にするためにやりたいことだけに焦点を当てるために、以下のようなrolesテーブルとusersテーブルがあるとしよう。

CREATE TABLE roles (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
  role INT FOREIGN KEY REFERENCE
);

ユーザーにロールを割り当てる典型パターン。
で、やりたいことは、

ロールの一覧表示をする際に、そのロールが割り当てられているユーザー数も出したい！

もちろん、SQL一発で簡単に取得できる。

SELECT roles.*,count(users.role) AS users
FROM roles,users
WHERE roles.id = users.role
GROUP BY roles.id;

このSQLをZend_Db_Selectで実装しようと思って、FROM句に2つのテーブルを並べるために

$select = $db->select();
$count = new Zend_Db_Expr('count(users.role)');
$select->from('roles')
       ->from('users', array('roles.*', 'users' => $count))
       ->where('roles.id = users.role')
       ->group('roles.id');
var_dump($select->__toString()); 

とやってみたが…

SELECT roles.*,count(users.role) AS users
FROM roles INNER JOIN users
WHERE roles.id = users.role
GROUP BY roles.id;

と、2つのテーブルがINNER JOINで結合するようなSQL文ができてしまった。

SQLを実行しても

ERROR:  syntax error at or near "WHERE"
LINE 3: WHERE roles.id = users.role
        ^

とエラーになってしまう（ちなみにRDBにはPostgreSQLを使っている）。

INNER JOINじゃなくてカンマでテーブル名を並べる方法はないものかとZend_Db_Selectのコードも調べてみたが

public function from($name, $cols = '*', $schema = null)
{    
    return $this->_join(self::FROM, $name, null, $cols, $schema);
}

となっているので、JOINする以外、選択の余地がないようで…。

最終的には、それぞれのテーブルに対応するZend_Db_Tableクラスを作って、それぞれのテーブルからfetchAll()して、PHP上で結合させるという方法に落ち着いた。SQLが2発実行されるのが嫌だけど、パフォーマンスがシビアになるWebアプリでもないので、コードの可読性を重視して。

class RolesController extends Zend_Controller_Action
{
  public function indexAction()
  {
    $table = new DbTable_Roles;
    $roles = array();
    $users = $this->_getRefUsers();
    foreach ($table->fetchAll($table->select()) as $row) {
      $role = (object)$row->toArray();
      $role->users = array_key_exists($role->id, $users) ? $users[$role->id] : 0;
      $roles[] = $role;
    }
    $this->view->roles = $roles;
  }

  private function _getRefUsers()
  {
    $table = new DbTable_Users;
    $count = new Zend_Db_Expr('count(role)');
    $select = $table->select()->from($table, array('role', 'users' => $count))->group('role');
    $users = array();
    foreach ($table->fetchAll($select) as $row) {
      $users[$row->role] = $row->users;
    }
    return $users;
  }
}