FuelPHPのクエリビルダでSQL Serverに対してoffsetとlimitを使用するためのメモ。

なお、FuelPHPからSQL Serverに接続するためのdb.phpの設定に関してはFuelPHPからSQL Serverへの接続するときのdb.phpの書き方に書いているので参照願いたい。

実行環境

• FuelPHP 1.7.3
• SQL Server 2014

MySQLなどではOFFSET, LIMITが利用できるが…

FuelPHPのQuery Builderでは、下記のようにすると30件目から10件分、などというデータ取得ができて便利だ。

DB::select('*')
->from('table')
->orderby('id')
->limit(10)
->offset(30)

しかし、RDBがSQL Serverだとこれができない。なぜなら単純にSQL ServerにはOFFSETやLIMITという構文が存在しないからだ。

なぜ未だにないのか理解に苦しむが、その代わりの構文は用意されている。

SQL ServerにおけるOFFSET FETCH句

[SQL Server] SQL Server 2008 以降で 結果レコードセットの指定した位置から指定した個数を取り出す – OFFSET FETCH の利用

その代わりの構文とはOFFSET n ROWS FETCH NEXT m ROWS ONLYである（SQL Server 2008以降のみ利用可能らしい）。

なんでこんなに冗長なのかまた理解に苦しむが、そういう仕様なのだから仕方ない。OFFSET n ROWS部分がOFFSETに、FETCH NEXT m ROWS ONLY部分がLIMITに相当する。

しかし、この構文には難点がある。LIMITだけの指定ができないのだ。これについては面倒だが、LIMITだけを指定したい場合はTOP句を利用するかOFFSET 0とすればよさそうだ。

というわけでFuelPHPのデータベースアクセス部分をこの仕様に沿ったように変更してやる必要がある。ただしこの変更を行うと逆にMySQLなどでlimitやoffsetを利用できなくなるので、SQL Serverと併用しなければならない場合は、他の方法を検討する必要がある。

select.phpの変更

FuelPHPのQueryBuilderでSELECT文を生成しているのはfuel/core/database/query/builder/select.phpである。このselect.phpのcompileメソッドが実際にクエリ文をコンパイルしているメソッドだ。

ただ、Coreのファイルを直接編集したくないので、appの下に同じ階層構造でselect.phpを作ってCoreの機能をオーバーライドする。

fuel/app/classes/database/query/builder/select.phpを作り、Database_Query_Builder_Selectクラスのcompileクラスのみをオーバーライドすればよい。

ほとんど元のcompileメソッドのコピーだが、最後のほうのoffsetとlimitに関する部分のみが異なっている。実装内容は下記の通り。

_distinct === TRUE) {
      $query .= 'DISTINCT ';
    }

    if (empty($this->_select)) {
      $query .= '*';
    } else {
      $query .= implode(', ', array_unique(array_map($quote_ident, $this->_select)));
    }

    if ( ! empty($this->_from)) {
      $query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from)));
    }

    if ( ! empty($this->_join)) {
      $query .= ' '.$this->_compile_join($db, $this->_join);
    }

    if ( ! empty($this->_where)) {
      $query .= ' WHERE '.$this->_compile_conditions($db, $this->_where);
    }

    if ( ! empty($this->_group_by)) {
      $query .= ' GROUP BY '.implode(', ', array_map($quote_ident, $this->_group_by));
    }

    if ( ! empty($this->_having)) {
      $query .= ' HAVING '.$this->_compile_conditions($db, $this->_having);
    }

    if ( ! empty($this->_order_by)) {
      $query .= ' '.$this->_compile_order_by($db, $this->_order_by);
    }

    if ($this->_offset !== NULL) {
      // Add offsets
      $query .= ' OFFSET '.$this->_offset.' ROWS ';
      if ($this->_limit !== NULL)
      {
        // Add limiting
        $query .= ' FETCH NEXT '.$this->_limit.' ROWS ONLY ';
      }
    }
    else if ($this->_limit !== NULL)
    {
      $query = preg_replace('/SELECT/', 'SELECT TOP '.$this->_limit, $query);
    }

    return $query;
  }
}
?>

offsetが指定されている場合はOFFSET n ROWS句を追加し、limitも指定されている場合はさらにFETCH NEXT n ROWS ONLYを追加する。

offsetが指定されずlimitが指定されている場合は、SELECTをSELECT TOP nに置き換える。

bootstrap.phpの変更

なお、このオーバーライドを有効にするにはfuel/app/bootstrap.phpのオートローダーにクラス定義を追加してやる必要がある。

fuel/app/bootstrap.phpを開き、Autoloader::add_classesの部分を次のように変更する。

Autoloader::add_classes(array(
     // Add classes you want to override here
     // Example: 'View' => APPPATH.'classes/view.php',
     'Database_Query_Builder_Select' => APPPATH.'classes/database/query/builder/select.php',
));

以上で、limitおよびoffsetメソッドが利用できるようになっているはずだ。