README

Laravel 手动 SELECT 查询分页

Laravel 手动分页 - Laravel Man Pagination

简介 - Laravel Man Pagination 包

此包对于使用 DB::select() 进行手动 SELECT 查询的分页非常有用。

Laravel Man Pagination 是一个开源的 PHP 库，实现了简单的手动分页（兼容 Bootstrap 5）。

使用这个分页，你将不会在 Laravel 项目中有限制地管理分页。

此分页还允许你生成每页的数量。这将生成一个带有可点击选项的 HTML 表单标签。

无限制地轻松分页 🚀

<?php

$pagination = new Pagination();

$pagination->paginate($totalElements); // $totalElements: result of an SQL COUNT query

$limit = $pagination->limit();
$offset = $pagination->offset();

// Here your manual SQL query with $limit and $offset

// Then your listing of elements with a loop

{!! $pagination->links() !!}
{!! $pagination->perPageForm() !!}

作者

此包由 Stephen Damian 开发。

要求

PHP 8.0 || 8.1 || 8.2 || 8.3
Laravel 8 || 9 || 10 || 11

安装

通过 Composer 安装

composer require s-damian/laravel-man-pagination

使用 "vendor:publish" 自定义

自定义配置、语言和 CSS

安装包后，你可以运行 vendor:publish 命令

php artisan vendor:publish --provider="SDamian\LaravelManPagination\ManPaginationServiceProvider"

vendor:publish 命令将生成以下文件

config/man-pagination.php
lang/vendor/man-pagination/{lang}/pagination.php
public/vendor/man-pagination/css/pagination.css

你当然可以自定义这些文件。

使用 "--tag" 参数的 "vendor:publish"

仅发布 config 文件

php artisan vendor:publish --provider="SDamian\LaravelManPagination\ManPaginationServiceProvider" --tag=config

仅发布 lang 文件

php artisan vendor:publish --provider="SDamian\LaravelManPagination\ManPaginationServiceProvider" --tag=lang

仅发布 CSS 文件

php artisan vendor:publish --provider="SDamian\LaravelManPagination\ManPaginationServiceProvider" --tag=css

分页实例方法

示例

带有手动 SELECT 查询 "DB::select" 的具体示例

在进行 "复杂" 的 SQL 查询时，有时你更愿意不使用 Eloquent 进行。

下面是一个此库真正有用的 SQL 查询示例。

$pagination = new Pagination();

$pagination->paginate($total);

$limit = $pagination->limit();
$offset = $pagination->offset();

$ordersAndInvoices = DB::select('
    SELECT
        for_type AS type,
        for_reference AS reference,
        for_first_name AS first_name,
        for_last_name AS last_name,
        for_tel AS tel,
        for_email AS email,
        for_amount AS amount

    FROM (

        (
            SELECT
                "Order" AS for_type,
                orders.reference AS for_reference,
                customers.first_name AS for_first_name,
                customers.last_name AS for_last_name,
                customers.tel AS for_tel,
                customers.email AS for_email,
                baskets.amount AS for_amount
            FROM orders
            INNER JOIN customers
                ON orders.customer_id = customers.id
            INNER JOIN baskets
                ON orders.id = baskets.order_id
            LIMIT '.$limit.' OFFSET '.$offset.'
        )

        UNION ALL
    
        (
            SELECT
                "Invoice" AS for_type,
                invoices.reference AS for_reference,
                customers.first_name AS for_first_name,
                customers.last_name AS for_last_name,
                customers.tel AS for_tel,
                customers.email AS for_email,
                invoices.amount AS for_amount
            FROM invoices
            INNER JOIN customers
                ON invoices.customer_id = customers.id
            LIMIT '.$limit.' OFFSET '.$offset.'
        )
    
    ) alias_ignored

    ORDER BY amount DESC
    LIMIT '.$limit.' OFFSET '.$offset
);

分页渲染示例

带有手动 SELECT 查询 "DB::select" 的简单示例

下面是一个如何使用此库的简单示例，遵循 MVC 模式。

控制器

<?php

use SDamian\LaravelManPagination\Pagination;

class CustomerController extends Controller
{
    public function index()
    {
        $sqlForCount = DB::select('
            SELECT COUNT(id) AS nb_customers
            FROM customers
        ');

        $total = $sqlForCount[0]->nb_customers;

        $pagination = new Pagination();

        $pagination->paginate($total);
        
        $limit = $pagination->limit();
        $offset = $pagination->offset();

        $customers = DB::select('
            SELECT *
            FROM customers
            ORDER BY id DESC
            LIMIT '.$limit.' OFFSET '.$offset.'
        ');

        return view('customer.index', [
            'customers' => $customers,
            'pagination' => $pagination,
        ]);
    }
}

视图

<div style="text-align: center;">
    @foreach ($customers as $customer)
        {{ $customer->id }}
        <br>
    @endforeach
</div>
            
<div style="text-align: center;">
    {{-- Show the pagination --}}
    {!! $pagination->links() !!}

    {{-- Show the per page --}}
    {!! $pagination->perPageForm() !!}
</div>

带有 Eloquent 的简单控制器示例

这只是一个示例。实际上，使用 Eloquent，你不需要这个库。因为你可以使用 Eloquent 的 paginate 方法。

<?php

use SDamian\LaravelManPagination\Pagination;

class CustomerController extends Controller
{
    public function index()
    {
        $total = Customer::count('id');

        $pagination = new Pagination();

        $pagination->paginate($total);
        
        $limit = $pagination->limit();
        $offset = $pagination->offset();

        $customers = Customer::skip($offset)->take($limit)->orderBy('id', 'desc')->get();

        return view('customer.index', [
            'customers' => $customers,
            'pagination' => $pagination,
        ]);
    }
}

向实例添加参数

<?php

// To change number of Elements per page:
$pagination = new Pagination(['pp' => 50]);
// Is 15 by default

// To change number of links alongside the current page:
$pagination = new Pagination(['number_links' => 10]);
// Is 5 by default

// To change the choice to select potentially generate with perPageForm():
$pagination = new Pagination(['options_select' => [5, 10, 50, 100, 500, 'all']]);
// The value of 'options_select' must be a array.
// Only integers and 'all' are permitted.
// Options are [15, 30, 50, 100, 200, 300] by default.

// To change the page name of the pagination in URL:
$pagination = new Pagination(['page_name' => 'p']);
// The page name is by default "page".

// To change the "per page" name of the pagination in URL:
$pagination = new Pagination(['per_page_name' => 'per_page']);
// The "per page" name is by default "pp".

// To change the CSS style of the pagination (to another CSS class as default):
$pagination = new Pagination(['css_class_p' => 'name-css-class-of-pagintion']);
// The CSS class name is by default "pagination".

// To change the CSS style of the pagination active (to another CSS class as default):
$pagination = new Pagination(['css_class_link_active' => 'name-css-class-of-pagintion']);
// The active CSS class name is by default "active".

// To change the CSS style of a per page (select) (to another id id as default):
$pagination = new Pagination(['css_id_pp' => 'name-css-id-of-per-page-form']);
// The CSS ID name is by default  "per-page-form".

与 Laravel 内置分页的区别

在这个分页中，我尽量保持了与 Laravel 内置分页的约定和行为。

我增加了额外的 "安全性"

例如，如果有 8 页，访问者在 URL 中尝试访问第 9 页（或第 9 页之后的页码）

onLastPage() 方法将返回 false（而 Laravel 内置分页会返回 true）。
currentPage() 方法将返回 1（而 Laravel 内置分页会返回 URL 中的页码）。

支持

错误和安全漏洞

如果你发现错误或安全漏洞，请给 Stephen 发送消息。谢谢。

所有错误和安全漏洞都将得到及时解决。

许可证

此项目是一个 MIT 许可下的开源包。有关详细信息，请参阅 LICENSE 文件。

s-damian / laravel-man-pagination

维护者

详细信息