使用Query Builder的代码点火器4中的分页问题

Question

我非常喜欢ci4和查询生成器，但我发现很难进行分页。

当检查到文档时，它只解释如何使用它，而模型没有查询生成器的解释。

我尝试在查询中使用手动分页和设置偏移量，但没有正常工作，而是返回下一个分页——它只为下一个页面返回一个结果，并将其加入到ckurent分页。我挑衅地被困在这里。

控制器：

$blog = new BlogModel();
//* Retrieve blogs
$total = $blog->getActiveBlogTotal();
$segment = route_to('adminBlogPost', 'active');
if($this->request->uri->getTotalSegments() == 4)
{
$page = $this->request->uri->getSegment(4);
}
else
{
$page = 1;
}
$perPage = 5;
$pages = $pager->makeLinks($page, $perPage, $total, 'admin_pag', 5);
$blogs = $blog->getActiveBlogsInAdmin($page);
return $this->smarty->view('blog/active.tpl', compact(
'blogs',
'pages'
)); 

型号：

//* Get blog in admin
public function getActiveBlogsInAdmin($page)
{
$table = $this->db->table('zd_blogs');
$table->select('*');
$table->join('zd_main_cats', 'zd_main_cats.main_cat_id = zd_blogs.blog_main_cat', 'left');
$table->join('zd_users', 'zd_users.user_id = zd_blogs.blog_user', 'left');
$table->where('zd_blogs.blog_status', 1);
$table->where('zd_blogs.blog_deleted', 0);
$table->orderBy('zd_blogs.blog_id', 'DESC');
$table->limit(5, $page);
$query = $table->get();
if($table->countAllResults() > 0)
{
return $query->getResult();
}
else
{
return FALSE;
}
} 

如果有人能好心帮我通过

Answer 1

不知道您是否已经找到了解决方案，但您可以按照以下步骤(对于CI4(

在控制器中执行以下操作。。

$pager=service('pager); //instantiate pager..
$page=(int)(($this>request->getVar('page)!==null)?$this->request->getVar('page'):1)-1; //limit  ie ?page=1 would be set to page=0;
$perPage =  5; //offset
$total = $blog->getActiveBlogTotal();
$pager->makeLinks($page,$perPage,$total);
$data['pager'] = $pager;

模型修改功能如下

public function getActiveBlogsInAdmin($page=0,$perPage=1000) and..
$table->limit($page, $perPage);

在视图中添加

<?= $pager->links() ?>

编辑：刚刚注意到你提到你正在使用生成器，在这种情况下，你可以删除

$table->limit($page,$perPage)

并使用

$query = $builder->get($page, $perPage); 

即极限和偏移量。

Answer 2

控制器：

$blog = new BlogModel();
//* Retrieve blogs
$segment = route_to('adminBlogPost', 'active');
$blogs = $blog->getActiveBlogsInAdmin($perPage);
$pager = $blog->pager;
return $this->smarty->view('blog/active.tpl', compact(
'blogs',
'pager'
)); 

型号：

public function getActiveBlogsInAdmin($perPage=10)
{
return $this->asArray()
->select('*')
->join('zd_main_cats', 'zd_main_cats.main_cat_id = zd_blogs.blog_main_cat', 'left')
->join('zd_users', 'zd_users.user_id = zd_blogs.blog_user', 'left')
->where('zd_blogs.blog_status', 1)
->where('zd_blogs.blog_deleted', 0)
->orderBy('zd_blogs.blog_id', 'DESC')
->pageinate($perPage);
}

使用CI 4.1.5和PHP 7.4.9，我尝试了一切可以在网上找到的东西，或者只是尝试随机的东西。事实证明，你只需要像任何简单的查找一样，在模型上链接查询生成器方法。然后内置的分页功能非常棒。

我确实找了这个，但只找到了错误的答案。然而，Paginate是模型的一部分，所以这是完全合理的。它需要张贴。祝有需要的人好运。

Answer 3

你似乎把事情搞混了。使用函数getActiveBlogsInAdmin()中的LIMIT子句，数据库查询限制为5个结果。因此，对于当前的分页设置，您将始终只有一页。

您需要返回完整的数据集，这样Pager类才能呈现First/Last/等。页面链接。

删除线路：$table->limit(5, $page);

Answer 4

我也有同样的问题。但我试着对它进行了修改，它运行得很好。你可以试试这个代替

• 型号

<?php
namespace AppModelsPersediaanBarang;

use CodeIgniterDatabaseBaseBuilder;
use CodeIgniterModel;

class M_pengadaan extends Model {
// ...
public function getData($per_page, $page, $filter) {
// Define builder
$builder = $this->db->table('t_req_pengadaan as TRP');
$builder->select('
TRP.*,
TL.nama_lab,
(SELECT COUNT(*) FROM t_req_pengadaan_detail TRPD WHERE TRPD.pengadaan_id = TRP.id) AS status_realisasi_wait,
(SELECT COUNT(*) FROM t_req_pengadaan_detail TRPD WHERE TRPD.pengadaan_id = TRP.id AND TRPD.status_realisasi = 2) AS status_realisasi_done
', false);
$builder->join('t_lab as TL', 'TRP.lab_id = TL.id', 'LEFT');                
if (isset($filter['status_realisasi_src']) && $filter['status_realisasi_src'] != '') {
if ($filter['status_realisasi_src'] == 1) {
$builder->having('status_realisasi_done = status_realisasi_wait');
} else if ($filter['status_realisasi_src'] == 2) {
$builder->having('status_realisasi_done < status_realisasi_wait AND status_realisasi_done != 0');
} else if ($filter['status_realisasi_src'] == 3) {
$builder->having('status_realisasi_done = 0');
}
}                  
// Custom Pagination
if ($page != 1) {
$offset = (($page - 1) * $per_page);
} else {
$offset = $page - 1;
}

if ($per_page != -1) {
$data_get = $builder->get($per_page, $offset);
} else {
$data_get = $builder->get();
}

return $data_get->getResult('object');
}
public function getDataRows($filter) {

// Define builder
$builder = $this->db->table('t_req_pengadaan as TRP');
$builder->select('
TRP.*,
TL.nama_lab,
(SELECT COUNT(*) FROM t_req_pengadaan_detail TRPD WHERE TRPD.pengadaan_id = TRP.id) AS status_realisasi_wait,
(SELECT COUNT(*) FROM t_req_pengadaan_detail TRPD WHERE TRPD.pengadaan_id = TRP.id AND TRPD.status_realisasi = 2) AS status_realisasi_done
', false);
$builder->join('t_lab as TL', 'TRP.lab_id = TL.id', 'LEFT');                
if (isset($filter['status_realisasi_src']) && $filter['status_realisasi_src'] != '') {
if ($filter['status_realisasi_src'] == 1) {
$builder->having('status_realisasi_done = status_realisasi_wait');
} else if ($filter['status_realisasi_src'] == 2) {
$builder->having('status_realisasi_done < status_realisasi_wait AND status_realisasi_done != 0');
} else if ($filter['status_realisasi_src'] == 3) {
$builder->having('status_realisasi_done = 0');
}
}    

return $builder->get()->getResult('object');
}
}

• 控制器

<?php

namespace AppControllersTransaksiPersediaanBarang;

use AppControllersBaseController;
use AppModelsPersediaanBarangM_pengadaan;
class ControllerPengadaan extends BaseController {

protected $m_pengadaan;
protected $pager;
public function __construct() {

$this->m_pengadaan = new M_pengadaan();
$this->pager = ConfigServices::pager();
}
public function index() {
// Pagination
$per_page = 10;

if (isset($_GET['page_custom']) && $_GET['page_custom'] != "") {
$page = ($_GET['page_custom']);
} else {
$page = 1;
}

// Passing data to View
$data['onLaporan'] = $this->m_pengadaan->getData($per_page, $page, $_GET);

$total_rows = count($this->m_pengadaan->getDataRows($_GET));
$data['pagination'] = $this->pager->makeLinks($page, $per_page, $total_rows);
echo view('transaksi/persediaan_barang/pengadaan/v_index', $data);
}
}

我希望你能从我的简单代码中得到帮助。

Answer 5

我最近开始使用codeigniter 4，并面临同样的问题。我已经按照Jason J.M的代码解决了这个问题。这是我的代码：

控制器：-

$myModel = new MyModel();
$contest_images = $myModel->my_method_name();
$pager=service('pager');
$page=(int)(($this->request->getVar('page')!==null)?$this->request->getVar('page'):1)-1;
$perPage =  12;
$total = count($contest_images);
$pager->makeLinks($page+1, $perPage, $total);
$offset = $page * $perPage;
$data['result'] = $myModel->my_method_name($perPage, $offset);

型号：-

public function my_method_name($perPage = null, $offset = null){
$builder = $this->db->table("my_db_table");
return $builder
->select()
->join('db_table_two','db_table_two.table_two_id = my_db_table.table_id')
->limit($perPage, $offset)
->get()
->getResult();
}

视图(分页代码(：-

<?php $pager = ConfigServices::pager(); ?>
<?php if ($pager):?>
<?php $pagi_path = 'my_controller/my_model'; ?>
<?php $pager->setPath($pagi_path); ?>
<?= $pager->links(); ?>
<?php endif; ?>