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
メソッドが利用できるようになっているはずだ。