Codeigniter v4 pagination Library

Question

我在github上找到了Codeigniter 4的分页库https://github.com/KejawenLab/CI4Pager。我遵循了每一步，但我在显示数据方面遇到了问题，我得到了所有的数据，甚至我提供了每页显示的数量限制。但对于分页链接，它是正确的。请帮忙，我如何限制结果的数量。

这是我的代码

//控制器

public function index()
{
helper('paginator');
$db = ConfigDatabase::connect();
$result = $db->query('SELECT * FROM users');
$paginator = AppLibrariesPaginator::createFromResult($result, 1, 1);
return view('page', ['paginator'=> $paginator]);
}

//查看

<?php foreach($paginator->getResults() as $row):?>
<div><?= $row['username'] ?></div>
<?php endforeach;?>
<?= ci_pager($paginator, [
'base_url' => '/vyombo',
'current_text' => 'Current Page',
'total_text' => 'Total Records',
]);
?>

Answer 1

不需要外部库。Codeigniter 4支持开箱即用的分页。

您可以对查询结果进行分页，也可以手动对数组进行分页。

https://codeigniter.com/user_guide/libraries/pagination.html?highlight=pagination

如果您在控制器中正确执行查询，那么这还远远不够理想。在您的情况下，您希望创建一个模型来处理该问题，然后使用该模型。

// In the Controller
public function index()
{
$userModel = new AppModelsUserModel();
$pageModel = new AppModelsPageModel();
$data = [
'users' => $userModel->paginate(10, 'group1'),
'pages' => $pageModel->paginate(15, 'group2'),
'pager' => $userModel->pager
];
echo view('users/index', $data);
}

然后在您的视图中，您可以获得完整链接或简单链接。

// In the views:
<?= $pager->links('group1') ?>
<?= $pager->simpleLinks('group2') ?>

如果你仍然想手动对查询进行分页，你可以这样做：

假设您有一个名为page=1的查询字符串。然后你需要每页10个元素。

只需执行以下查询即可：

$limit = 10;
$offset = 10 * ($_GET['page'] - 1); 
SELECT *
FROM articles
LIMIT $limit, $offset;

然后要打印视图中的分页链接，您只需使用：

<?= $pager->makeLinks($_GET['page'], $limit, $total) ?>

因此，对于分页，您总是需要两个查询。一个会给你当前页面，另一个会告诉你总结果。

Answer 2

通过使用SQL语句"创建或替换视图VIEW _ province_；，并将表属性设置为模型函数protected$table='default_table'；但是替换表内部的方法。像"$这->table＝"视图_收入"；其中view_provice_enerows会自动添加到您的数据库中，并保存您的命令数据。

//型号

public function getData()
{
//reset table to be used as default
$this->table = 'view_province_incomes';
//creating view table to your database to hold the data
$sql = "CREATE OR REPLACE VIEW view_province_incomes AS ";
$sql .= "
SELECT Date, 
SUM(Income) as Income, 
SUM(Expense) as Expense, 
SUM(Income) - SUM(Expense) as Profit 
FROM ( 
SELECT 
province_income.date as Date, 
province_income.amount as Income, 
0 as Expense 
FROM province_income
LEFT JOIN province ON province.provId = province_income.provId
WHERE province_income.provId = 5
UNION ALL 
SELECT 
province_expenses.date as Date, 
0 as Income, 
province_expenses.amount as Expense 
FROM province_expenses
LEFT JOIN province ON province.provId = province_expenses.provId
WHERE province_expenses.provId = 5 
) as t 
GROUP BY Date
";

$this->db->query($sql);
return $this; //to make easy chaining with the instance
}

//控制器

public function index()
{
$data['results'] = $this->model->getData()->asObject()->paginate(4);
$data['pager']   = $this->model->pager->links();
return view('Income/index', $data);
}

//视图

<?php foreach ($results as $result): ?>
<div style="display: flex; justify-content: space-between;">
<p> <?= $result->Date ?></p>
<p> <?= $result->Income ?></p>
<p> <?= $result->Expense ?></p>
<p> <?= $result->Profit ?></p>
</div>
<?php endforeach;?>
<!-- pagination links -->
<?= $pager ?>

//输出

+------------+-----------------------------+
| Date       | Incomes  | Expense | Profit |
+------------+----------+---------+--------+
| 2018-06-12 | 10000    | 5000    | 5000   | 
+------------+----------+---------+--------+
| 2018-06-14 | 17000    | 9000    | 8000   | 
+------------+----------+---------+--------+
| 2018-06-15 | 8000     | 6000    | 2000   |
+------------------------------------------+