c0d3man52

Webサイト制作

[CodeIgniter] SQL_CALC_FOUND_ROWSを使って一覧と件数を一発で取得する

CodeIgniterで、一覧を取得するコードを書く場合に、一覧と合計件数を一発で取得する書き方を考えてみました。

更新日: 2018.7.17公開日: 2017.10.24

目次

今までは一覧と合計件数を別々に取得していました

これまでのやり方

  1. 一覧取得を（LIMITで絞り込み） ->getList
  2. 合計件数を取得 ->getCount

同じような内容のメソッドを二つ書くので面倒&メンテナンス性が低いのが最大のデメリットです。全くもってスマートではありませんね。

新しいやり方

  1. 一覧（LIMITで絞り込み）&&合計件数を一気に取得 ->getList

一つのメソッドで一覧と合計件数を取得するのでメンテナンス性が向上しただけでなく、コードも減ります。

このやり方を採用するにあたって、MYSQLの「SQL_CALC_FOUND_ROWS」を使います。CodeIgniterのデータベースをSQLITEにしている方は、残念ながらSQL_CALC_FOUND_ROWSがSQLITEにはない模様です。

SQL_CALC_FOUND_ROWSとは？

MYSQLで使える「SQL_CALC_FOUND_ROWS」コマンドは、直前に発行したSQLのLIMITなしの件数をキャッシュしてくれるMYSQLのコマンドです。

ステートメントを再度実行せずに、LIMIT を付けなかった場合にステートメントで返されるはずの行数を知っておくことが望ましいことがあります。この行数を取得するには、SELECT ステートメントに SQL_CALC_FOUND_ROWS オプションを付けてから、FOUND_ROWS() を呼び出します。
FOUND_ROWS | 12.14 情報関数

CodeIgniterでも「SQL_CALC_FOUND_ROWS」と「FOUND_ROWS」は使えます。

実装

ではやり方を見てみます。

記事一覧を取得するモデルメソッド「getLists」を作ってみます。

コード

vi Article_model.php

<?php
class Article_model extends CI_Model {

  function __construct(){
    parent::__construct();
  }

  function getLists($perpage, $offset){
    $this->DB1 = $this->load->database('default', TRUE, TRUE);
    $this->DB1->select('SQL_CALC_FOUND_ROWS *', FALSE);
    $this->DB1->from('articles');
    $this->DB1->limit($perpage, $offset);
    $query = $this->DB1->get();

    $data["lists"] = $query->result_array();//結果一覧
    $data["count"] = $this->DB1->query('SELECT FOUND_ROWS() AS cnt')->row()->cnt;//全件数
    return $data;
  }
}

簡単に解説

SQLをActive Recordで作る流れは通常のCodeIgniterと同じですが、select句の指定に違いがあります。

$this->DB1->select('SQL_CALC_FOUND_ROWS *', FALSE);

この部分ですね。頭に「SQL_CALC_FOUND_ROWS」と入れると、引数2に「FALSE」を入れるのがミソです。

idなどカラムを指定する場合は、

$this->DB1->select('SQL_CALC_FOUND_ROWS id, title, text', FALSE);

とすればOKです。

あとは、一覧のほうは、通常通り、

$query = $this->DB1->get();
$query->result_array();//結果一覧

で取得できます。

件数のほうは、もう一度SQLを発行して取得する形になるのですが、先に取得したクエリで「SQL_CALC_FOUND_ROWS」を宣言しているので、「FOUND_ROWS()」をSELECTするだけで件数が取得できます。

$this->DB1->query('SELECT FOUND_ROWS() AS cnt')->row()->cnt;//全件数

今回は一つのメソッドで完結させたいので、返す値は連想配列で返します。

$data["lists"] = $query->result_array();//結果一覧
$data["count"] = $this->DB1->query('SELECT FOUND_ROWS() AS cnt')->row()->cnt;//全件数
return $data;

あとは、コントローラー側から呼び出せばOKです。

$articles = $this->Article_model->getLists(10, 0);

var_dump($articles["lists"]);
var_dump($articles["count"]);

ちなみに、結局のところSQLを2回発行していることには変わらないので、MYSQLの速度やサーバーへの負荷はあまり変わりません。ただ、コードの視認性とメンテナンス性が上がるのは大きなメリットだと思います。

ちなみに、SELECT FOUND_ROWS()を使わない場合は、CodeIgniterの「クエリビルダ キャッシング」を利用することもできます。そちらはまた別の記事で。

クエリビルダキャッシング版
==> [CodeIgniter] クエリビルダ(Active Record)のキャッシングを使って開発効率をアップする

タグ