标签归档:分页

Laravel 分页详解

对大数据集合进行分页的操作非常的常见。以至于必须封装成一个傻瓜式的组件。分页永远离不开两个东西,1、分页器,2、渲染器。Laravel中出现构建器或者模型都提供了paginate(和simplePaginate)方法,它就是返回一个分页器。话说,分页包含什么,当然是当前页面的数据集合和分页相关的信息(比如页大小,当前页码,总页数等等这类信息),而渲染器就用这个分页器的信息构建输出。

我们从查询构建起的paginate方法进入:

// 方法实现
    public function paginate($perPage = 15, $columns = ['*'], $pageName = 'page', $page = null)
    {
        $page = $page ?: Paginator::resolveCurrentPage($pageName);

        $total = $this->getCountForPagination($columns);

        $results = $this->forPage($page, $perPage)->get($columns);

        return new LengthAwarePaginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
            'pageName' => $pageName,
        ]);
    }

// 例子 
$articles = DB::table("articles")->select('articles.title','articles.body','comments.page_id','comments.id')
        ->leftJoin("comments","articles.id","=","comments.page_id")
        ->paginate(5);

方法paginate()第一参数指定了页大小(默认就是15),第二参数指定了要哪些字段(一般在查询时指定),第三参数指定了页码名称,第四就是页码。实际的例子只指定了第一参数(页大小),那么页码是如何取到的?(玛尼的),好吧,它有一个服务提供者,在框架初始化时被load进来(app.php中定义)

class PaginationServiceProvider extends ServiceProvider
{
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        Paginator::currentPathResolver(function () {
            return $this->app['request']->url();
        });

        Paginator::currentPageResolver(function ($pageName = 'page') {
            return $this->app['request']->input($pageName);
        });
    }
}

Illuminate\Pagination\Paginator的静态方法currentPathResolver和currentPageResolver,分别把当前的路径(?号之前)和 获取到当前页。回头看那个paginate()方法,里面有Paginator::resolveCurrentPage()和resolveCurrentPath(),最后放回一个LengthAwarePaginator()的实例,这个东西的方法,大部分来自Illuminate\Pagination\AbstractPaginator,打开看看就一目了然了。

分页器的实例已经获取到了,那么渲染器是如何工作的呢?先看一个实例:

$articles = DB::table("articles")->select('articles.title','articles.body','comments.page_id','comments.id')
        ->leftJoin("comments","articles.id","=","comments.page_id")
        ->paginate(5);
        
return view('test.test', ['articles' => $articles]);
//视图
<div class="container">
    @foreach ($articles as $article)
        {{ $article->title }}
        <br />
    @endforeach
</div>

{!! $articles->render() !!}

好吧,一切尽在分页器中。可以直接foreach它,可以直接调用它的render()渲染输出,重点在render()方法:

    public function render(Presenter $presenter = null)
    {
        if (is_null($presenter) && static::$presenterResolver) {
            $presenter = call_user_func(static::$presenterResolver, $this);
        }

        $presenter = $presenter ?: new BootstrapThreePresenter($this);

        return $presenter->render();
    }

如果没有提供渲染器,检查static::$presenterResolver,它需要给出一个闭包函数,这个闭包函数需要一个参数(最终使用具体的实例传入这个这个闭包),返回一个渲染器。否则就整一个默认的BootstrapThreePresenter($this)给你。针对static::$presenterResolver,几乎没有悬念,必定有那么一个方法设置它,方法在AbstractPaginator抽象类中:

    public static function presenter(Closure $resolver)
    {
        static::$presenterResolver = $resolver;
    }

这样,我们定义一个自定义的渲染器:

<?php

namespace App\Help;

use Illuminate\Pagination\BootstrapThreePresenter;

class MyPagination extends BootstrapThreePresenter {
    public function getActivePageWrapper($text) {
        return '<div class="active item">' . $text . '</div>';
    }
    public function getDisabledTextWrapper($text) {
        return '<div class="disabled item">' . $text . '</div>';
    }
    public function getAvailablePageWrapper($url, $page, $rel = null) {
        return '<a href="' . $url . '" class="item">' . $page . '</a>';
    }
    public function render() {
        if ($this->hasPages ()) {
            return sprintf ( '%s %s %s', $this->getPreviousButton (), $this->getLinks (), $this->getNextButton () );
        }
        
        return '';
    }
}

// 补充以下实例
$articles = DB::table("articles")->select('articles.title','articles.body','comments.page_id','comments.id')
        ->leftJoin("comments","articles.id","=","comments.page_id")
        ->paginate(5);

// render()就可应用自定义的渲染器
LengthAwarePaginator::presenter(function($articles){
    return new MyPagination($articles);
});
//
        
return view('test.test', ['articles' => $articles]);
//视图
<div class="container">
    @foreach ($articles as $article)
        {{ $article->title }}
        <br />
    @endforeach
</div>

{!! $articles->render() !!}

这样,自定义的分页渲染器就弄好了。如果不希望每次都搞一次,可以把代码写入App\Providers\AppServiceProvider的register方法中:

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Pagination\LengthAwarePaginator;
use App\Helper\MyPagination;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
    }

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
        LengthAwarePaginator::presenter(function($pager){
            return new MyPagination($pager);
        });
    }
}

有时候,分页链接需要携带自定义参数:

$articles = DB::table("articles")->select('articles.title','articles.body','comments.page_id','comments.id')
        ->leftJoin("comments","articles.id","=","comments.page_id")
        ->paginate(5);
$articles->appends(['xx'=>'xx','oo'=>'oo']);

// 产生的链接如下
http://laravel.ifeeline.com/test/test?xx=xx&oo=oo&page=2

以下是分页器的方法列表:

    $results->count()
    $results->currentPage()
    $results->hasMorePages()
    $results->lastPage() (使用simplePaginate时无效)
    $results->nextPageUrl()
    $results->perPage()
    $results->total() (使用simplePaginate时无效)
    $results->url($page)

使用场景,获取当前请求URL:

// 先取回当前页码
$page = $results->currentPage();
// 然后传入url
$results->url($page)
// http://laravel.ifeeline.com/test/test?xx=xx&oo=oo&page=2

有时候为了使用Ajax进行分页,那么就需要返回一个JSON,而分页器实现了Illuminate\Contracts\Support\JsonableInterface,所以可以调用toJson()方法输出JSON数据:

{
   "total": 50,
   "per_page": 15,
   "current_page": 1,
   "last_page": 4,
   "next_page_url": "http://laravel.app?page=2",
   "prev_page_url": null,
   "from": 1,
   "to": 15,
   "data":[
        {
            // Result Object
        },
        {
            // Result Object
        }
   ]
}

Laravel的分页实现就是这样了。平心而论,也没有多先进。很多的实现思路都是这样。

Zend\Paginator 使用实例

分页在应用开发中是必不可少的,Zend\Paginator提供的分页组件,总体感觉还算不错。数据集合分页本身的逻辑并不复杂,三个输入参数:页大小,页号,总记录数,就可以计算当前页数据已经分页导航(前一页后一页等),但是由于使用太频繁了,把这个抽象出来总是由价值的。

以下是测试例子:

<?php
$rootPath = dirname(__DIR__);
$library = $rootPath."/library";

chdir($rootPath);
if(file_exists('vendor/autoload.php')){
    $loader = include '/vendor/autoload.php';
}else{
    exit("Autoload Failed. ");
}
$loader->setPsr4("Zend\\",$library.'/zf2_psr4');

$dbAdapter = new Zend\Db\Adapter\Adapter(array(
    'hostname' => 'localhost',
    'database' => 'test',
    'username' => 'root',
    'password' => '',

    //     'driver' => 'mysqli',
    //     'charset' =>'utf8'

    'driver' => 'pdo_mysql',
    'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
    ),
));

/// 0 初始化
$query = $_GET;
unset($query['page']);

// 0 - 1 数组
// $array = array();
// for($i=0;$i<666;$i++) {
//     $array[] = array("first_name"=>"ifeeline".$i,"last_name"=>"vvvvvvv".$i);
// }
// $arrayAdapter = new Zend\Paginator\Adapter\ArrayAdapter($array);
// $paginator = new Zend\Paginator\Paginator($arrayAdapter);


// 0 - 2 DbSelect
// $select = new Zend\Db\Sql\Select();
// $select->from("datatables_demo")->columns(array("id","first_name","last_name","email"))
// ->where("age > 20");

// $dbSelectAdapter = new Zend\Paginator\Adapter\DbSelect($select, $dbAdapter);
// $paginator = new Zend\Paginator\Paginator($dbSelectAdapter);

// 0 - 3 TableGateway
$tableGateway = new Zend\Db\TableGateway\TableGateway("datatables_demo", $dbAdapter);
$tableGatewayAdapter = new Zend\Paginator\Adapter\DbTableGateway($tableGateway);
$paginator = new Zend\Paginator\Paginator($tableGatewayAdapter);


//$paginator->setDefaultScrollingStyle("All");
//$paginator->setDefaultScrollingStyle("Jumping");
$paginator->setDefaultScrollingStyle("Elastic");
//$paginator->setDefaultScrollingStyle("Sliding"); 默认

/// 1 配置
// 设置显示的页码范围
$paginator->setPageRange(10);

/// 2 修正当前页码
// 总页数
$totalPage = count($paginator);

// 获取当前页码
$page = !empty($_GET['page'])?(int)$_GET['page']:1;
if($page < 1) {
    $page = 1;
} else if($page > $totalPage) {
    $page = $totalPage;
}
$query['page'] = $page;


// 设置当前页码
$paginator->setCurrentPageNumber($page);

/// 3 取得页数据
$datas = $paginator->getCurrentItems();

/// 4 获取页码控制器
$pageCtrl = $paginator->getPages();

/// 5 页码控制器输出
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>

<table style="width:500px;">
    <tr style="backgroup-color:#ccc">
        <td style="width:150px; text-align:left;">名称</td>
        <td style="width:150px; text-align:left;">值</td>
        <td>操作</td>
    </tr>
<?php
if(!empty($datas)) {
    foreach($datas as $row) {
?>
    <tr>
        <td style="text-align:left;"><?php echo $row['first_name'];?></td>
        <td style="text-align:left;"><?php echo $row['last_name'];?></td>
        <td style="text-align:left;">编辑 更新 删除</td>
    </tr>
<?php 
    }
}
?>
</table>

<br /><br />

<?php 
if ($pageCtrl->pageCount): ?>
<div class="paginationControl">
<!-- Previous page link -->
<?php if (isset($pageCtrl->previous)): 
    $query['page'] = $pageCtrl->previous;
?>
  <a href="<?php echo "paginator.php?".http_build_query($query);?>">
    前一页
  </a> |
<?php else: ?>
  <span class="disabled">前一页</span> |
<?php endif; ?>

<!-- Numbered page links -->
<?php foreach ($pageCtrl->pagesInRange as $pageIndex): ?>
  <?php if ($pageIndex != $pageCtrl->current): 
        $query['page'] = $pageIndex;
  ?>
    <a href="<?php echo "paginator.php?".http_build_query($query);?>">
        <?php echo $pageIndex; ?>
    </a> |
  <?php else: ?>
    <?php echo $pageIndex; ?> |
  <?php endif; ?>
<?php endforeach; ?>

<!-- Next page link -->
<?php if (isset($pageCtrl->next)): 
        $query['page'] = $pageCtrl->next;
?>
  <a href="<?php echo "paginator.php?".http_build_query($query);?>">
    下一页
  </a>
<?php else: ?>
  <span class="disabled">下一页</span>
<?php endif; ?>
</div>
<?php endif;?>

<br /><br />

<?php 
if ($pageCtrl->pageCount): ?>
<div class="paginationControl">
    <?php echo $pageCtrl->firstItemNumber;?> - <?php echo $pageCtrl->lastItemNumber;?> of <?php echo $pageCtrl->totalItemCount;?>

    <!-- First page link -->
    <?php 
    if (isset($pageCtrl->previous)): 
        $query['page'] = $pageCtrl->first;
    ?>
    <a href="paginator.php?<?php echo http_build_query($query);?>">首页</a> |
    <?php else: ?>
    <span class="disabled">首页</span> |
    <?php endif; ?>

    <!-- Previous page link -->
    <?php if (isset($pageCtrl->previous)): 
        $query['page'] = $pageCtrl->previous;
    ?>
      <a href="paginator.php?<?php echo http_build_query($query);?>">
                     前一页
      </a> |
    <?php else: ?>
      <span class="disabled">前一页</span> |
    <?php endif; ?>

    <!-- Numbered page links -->
    <?php foreach ($pageCtrl->pagesInRange as $pageIndex): ?>
      <?php if ($pageIndex != $pageCtrl->current): 
            $query['page'] = $pageIndex;
      ?>
        <a href="<?php echo "paginator.php?".http_build_query($query);?>">
            <?php echo $pageIndex; ?>
        </a> |
      <?php else: ?>
        <?php echo $pageIndex; ?> |
      <?php endif; ?>
    <?php endforeach; ?>
    
    <!-- Next page link -->
    <?php if (isset($pageCtrl->next)): 
        $query['page'] = $pageCtrl->next;
    ?>
      <a href="paginator.php?<?php echo http_build_query($query);?>">
                     下一页
      </a> |
    <?php else: ?>
      <span class="disabled">下一页</span> |
    <?php endif; ?>
    
    <!-- Last page link -->
    <?php if (isset($pageCtrl->next)): 
        $query['page'] = $pageCtrl->last;
    ?>
      <a href="paginator.php?<?php echo http_build_query($query);?>">
                    末页
      </a>
    <?php else: ?>
      <span class="disabled">末页</span>
    <?php endif; ?>
</div>
<?php endif; ?>

<br /><br />

<?php if ($pageCtrl->pageCount): ?>
<select id="paginationControl" size="1">
<?php foreach ($pageCtrl->pagesInRange as $pageIndex): ?>
  <?php $selected = ($pageIndex == $pageCtrl->current) ? ' selected="selected"' : ''; ?>
  <option value=""<?php echo $selected ?>>
    <?php echo $pageIndex; ?>
  </option>
<?php endforeach; ?>
</select>
<?php endif; ?>
</body>
</html>

paginator

PHP框架Phalcon 之 分页(Pagination)

Phalcon Paginator
Phalcon\Paginator\AdapterInterface有两个方法,setCurrentPage()和getPaginate()。
Phalcon\Paginator\Adapter\NativeArray 和 Phalcon\Paginator\Adapter\Model感觉很鸡肋,特别是Phalcon\Paginator\Adapter\Model,它把所有符合条件的记录全部返回填充,换句话说就是,如果有100条记录,我当前只显示20条,那么它把100条全部返回,如果翻到下一页,也是100条全部返回,然后定位到需要的记录,所以这里不讨论这两个东西。

//config/services.php中注册如下服务
	//缓存元数据
    $di->set('modelsMetadata',function(){
        $metaData = new \Phalcon\Mvc\Model\Metadata\Files(array(
                'metaDataDir' => __DIR__.'/../apps/cache/metadata/'
        ));
        return $metaData;
    });

	//设置数据库链接 记录查询
    $di->set('db', function() use($di) {
        $eventsManager = $di->get("eventsManager");
    

        $logger = new \Phalcon\Logger\Adapter\File(__DIR__."/../apps/logs/debugs.log");
        $eventsManager->attach('db', function($event, $connection) use ($logger) {
                if ($event->getType() == 'beforeQuery') {
                        $logger->log($connection->getSQLStatement(), \Phalcon\Logger::INFO);
                }
                if ($event->getType() == 'beforeSave') {
                        $logger->log($connection->getSQLStatement(), \Phalcon\Logger::INFO);
                }
        });

        $connection =  new \Phalcon\Db\Adapter\Pdo\Mysql(array(
            "host" => "127.0.0.1",
            "username" => "root",
            "password" => "root",
            "dbname" => "ai_manage",
            "charset" => "utf8"
        ));

        $connection->setEventsManager($eventsManager);
        return $connection;
    },false);

然后写一个分写查询:

//控制器动作
public function builderAction(){
        $currentPage = $this->request->getQuery("page","int",1);
        
        $builder = $this->modelsManager->createBuilder()
                ->columns('id, order_id')
                ->from('Vf\Frontend\Models\AiOrder')
                ->orderBy('id');

        $paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
                "builder" => $builder,
                "limit"=> 15,
                "page" => $currentPage
        ));     
        $this->view->setVar("page",$paginator->getPaginate());

}

//视图文件
<table width="50%" border="1">
<?php
foreach($page->items as $item){
?>
	<tr><td><?php echo $item->id;?></td><td><?php echo $item->order_id;?></td></tr>
<?php
}
?>
</table>

<span>
<?php
$cp = $this->request->getQuery("page","int",1);

if($cp == 1){
}else{
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>1));?>">First</a>
<?php
}

if($cp == 1){
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>$page->last));?>">Previous</a>
<?php
}else{
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>$page->before));?>">Previous</a>
<?php
}

if($cp == $page->last){
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>$page->first));?>">Next</a>
<?php
}else{
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>$page->next));?>">Next</a>
<?php
}
if($cp == $page->last){
}else{
?>
<a href="<?php echo $this->url->get("index/builder",array("page"=>$page->last));?>">Last</a>
<?php
}
?>
<?php echo "You are in page ", $page->current, " of ", $page->total_pages; ?>
</span>

输出:
Phalcon分页器

一共有762页,每页15条记录,总记录超过1万。发现翻页奇慢。查看SQL输出:

##默认第一页
[Wed, 10 Sep 14 13:57:48 +0800][INFO] SELECT `ai_order`.`id` AS `id`, `ai_order`.`order_id` AS `order_id` FROM `ai_order` ORDER BY `ai_order`.`id` LIMIT 15
[Wed, 10 Sep 14 13:57:56 +0800][INFO] SELECT COUNT(*) "rowcount" FROM (SELECT ai_order.* FROM `ai_order`) AS T
##翻到第二页
[Wed, 10 Sep 14 13:58:07 +0800][INFO] SELECT `ai_order`.`id` AS `id`, `ai_order`.`order_id` AS `order_id` FROM `ai_order` ORDER BY `ai_order`.`id` LIMIT 15 OFFSET 15
[Wed, 10 Sep 14 13:58:08 +0800][INFO] SELECT `ai_order`.`id` AS `id`, `ai_order`.`order_id` AS `order_id` FROM `ai_order` ORDER BY `ai_order`.`id` LIMIT 15 OFFSET 15
[Wed, 10 Sep 14 13:58:26 +0800][INFO] SELECT COUNT(*) "rowcount" FROM (SELECT ai_order.* FROM `ai_order`) AS T
[Wed, 10 Sep 14 13:58:26 +0800][INFO] SELECT COUNT(*) "rowcount" FROM (SELECT ai_order.* FROM `ai_order`) AS T 

缓慢的原因:

mysql> SELECT COUNT(*) "rowcount" FROM ai_order;
+----------+
| rowcount |
+----------+
|    11425 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*) "rowcount" FROM (SELECT ai_order.* FROM `ai_order`) AS T;
+----------+
| rowcount |
+----------+
|    11425 |
+----------+
1 row in set (7.40 sec)

翻页时如下语句:

SELECT COUNT(*) "rowcount" FROM (SELECT ai_order.* FROM `ai_order`) AS T

还执行两次。每次执行花了7.4秒,执行两次公14.8秒。就是翻个页要15秒,简直蛋疼。至于为何要执行子查询而不是直接使用统计函数,就不得而知了,所以为了改善这个组件,看来只能覆盖getPaginate()方法了,我试着进行了改写,可参考:https://github.com/vfeelit/Phalcon_Paginator。

在https://github.com/phalcon/incubator/tree/master/Library/Phalcon中有提供一个叫Pager的分页器,类似如下:
Phalcon Pager分页器

<?php
namespace Vf\Frontend\Controllers;

use Vf\Frontend\Controllers\ControllerBase;
use Phalcon\Paginator\Pager;
use Phalcon\Paginator\Adapter\NativeArray as Paginator;

class IndexController extends ControllerBase
{
    public function indexAction(){
        $currentPage = $this->request->getQuery("page","int",1);

        $builder = $this->modelsManager->createBuilder()
                ->columns('id, order_id')
                ->from('Vf\Frontend\Models\AiOrder')
                ->orderBy('id');

        $pager = new Pager(
            new \Vfeelit\Paginator\Adapter\QueryBuilder(array(
                "builder" => $builder,
                "limit"=> 10,
                "page" => $currentPage
            )),
            array(
                // We will use Bootstrap framework styles
                'layoutClass' => '\Phalcon\Paginator\Pager\Layout\Bootstrap',
                // Range window will be 5 pages
                'rangeLength' => 10,
                // Just a string with URL mask
                'urlMask'     => $this->url->get("index/index").'?page={%page_number}',
                // Or something like this
                // 'urlMask'     => sprintf(
                //     '%s?page={%%page_number}',
                //     $this->url->get(array(
                //         'for'        => 'index:posts',
                //         'controller' => 'index',
                //         'action'     => 'index'
                //     ))
                // ),
            )
        );
        $this->view->setVar('pager', $pager);
    }
}

//对应视图
<table style="border:1px solid #ccc; border-collapse:collapse;">
<?php
foreach($pager->getIterator() as $p){
?>
<tr><td><?php echo $p->id;?></td><td><?php echo $p->order_id;?></td></tr>
<?php
}
?>
</table>
<?php
if($pager->haveToPaginate()){
        echo $pager->getLayout();
}

Phalcon提供的分页器相比其它框架,可以说是很不错的了。但是在针对大数据集时还是有点问题。关于这个内容就讨论这些了。