chandra-hemant/server-side-datatable

用于服务器端 DataTables 辅助的 Laravel 扩展包

维护者

详细信息

github.com/ChandraHemant/server-side-datatable

源代码

问题

安装: 165

依赖: 0

建议者: 0

安全性: 0

星级: 0

关注者: 1

分支: 0

开放问题: 0

v3.0.0 2024-09-05 06:02 UTC

Requires

  • php: ^8.1

MIT b26a5e744d9bb62cd85b9a6c6f75f1fc0609efad

  • Hemant Chandra <hemantchandra242.woop@gmail.com>

This package is not auto-updated.

Last update: 2024-09-28 05:55:38 UTC

README

本包为 Laravel 应用程序中的服务器端 DataTables 提供了一种方便的数据获取方法。

安装

您可以通过 Composer 安装此包。

composer require chandra-hemant/server-side-datatable

概述

ChandraHemant/ServerSideDatatable 包提供了一组辅助方法，用于从数据库表检索数据，并可选择连接多个表。此包旨在简化 Laravel 应用程序中 DataTables 的服务器端处理。

类概述

命名空间：ChandraHemant\ServerSideDatatable
作者：Hemant Kumar Chandra

DynamicModelDataTableHelper 类

DynamicModelDataTableHelper 类提供了一组用于从数据库表检索和操作数据的方法，特别适用于与 Laravel 的 Eloquent ORM 一起使用。本指南概述了如何有效地利用这些方法来实现具有动态条件、分页和搜索功能的服务器端数据表。

用法

检索数据

您可以使用 getServerSideDataTable 方法从您的数据库表中检索数据。

use App\Models\YourModel; // Import your Eloquent model

$dynamicConditions = [
    // Specify your dynamic conditions here
];
$searchColumns = [
    // Specify your searchable columns here
];
$searchRelationships = [
    // Specify your dynamic relationships here
];
$helper = new DynamicModelDataTableHelper(
    eloquentModel: new YourModel(),
    dynamicConditions: $dynamicConditions,
    searchColumns: $searchColumns,
    searchRelationships: $searchRelationships
);

$result = $helper->getServerSideDataTable();

计算过滤记录数

为了在不获取所有数据的情况下计算过滤记录数，您可以使用 countFilteredServerSideDataTable 方法。

use App\Models\YourModel; // Import your Eloquent model

$dynamicConditions = [
    // Specify your dynamic conditions here
];
$searchColumns = [
    // Specify your searchable columns here
];
$searchRelationships = [
    // Specify your dynamic relationships here
];
$helper = new DynamicModelDataTableHelper(
    eloquentModel: new YourModel(),
    dynamicConditions: $dynamicConditions,
    searchColumns: $searchColumns,
    searchRelationships: $searchRelationships
);

$result = $helper->countFilteredServerSideDataTable();

动态条件

'dynamicConditions' 参数允许您根据各种条件自定义数据库查询。这些条件可以包括排序、选择特定列、基于关系进行过滤等。

 $dynamicConditions = [
    [
        'method' => 'whereColumn',
        'args' => ['column1', '>=', 'column2'],
        'condition' => 'loss'
    ],
    [
        'method' => 'whereRaw',
        'args' => ['YEAR(column3) = ?', session()->get('financialYear')]
    ],
    [
        'method' => 'whereIn',
        'args' => ['column4', session()->get('values')]
    ],
    [
        'method' => 'where',
        'args' => ['column6', 0]
    ],
    [
        'method' => 'whereHas',
        'args' => ['relation1', function ($query1) {
            $query1->where('column1', '=', 4);
        }]
    ],
    [
        'method' => 'select',
        'args' => ['column1','column2','column3','column4','column5','column6','column7','column8','column9'],
        'relation' => ['relation1','relation2','relation3','relation4','relation5','relation6','relation7','relation8','relation9'],
    ],
    [
        'method' => 'orderBy',
        'args' => ['column1','column2','column3','column4','column5','column6','column7','column8','column9']
    ]
];

搜索功能

您可以通过提供要搜索的列和关系来启用搜索功能。

// Define search value, columns, and relationships
$searchColumns = ['column1','column2','column3','column4','column5','column6','column7','column8','column9'];

$searchRelationships = [
    'relation1' => ['column1'],
    'relation2' => ['column2'],
    'relation3' => ['column3'],
    'relation4' => ['column4'],
    'relation5' => ['column5'],
    'relation6' => ['column6'],
    'relation7' => ['column7'],
    'relation8' => ['column8'],
    'relation9' => ['column9'],
];

分页

分页将根据请求参数自动应用。

构造函数参数

  • $eloquentModel (Illuminate\Database\Eloquent\Model)：查询数据的 Eloquent 模型。
  • $dynamicConditions (数组)：指定查询的动态条件数组。
  • $searchColumns (数组)：指定要搜索的列数组。
  • $searchRelationships (数组)：指定要搜索的关系数组。

方法

getServerSideDataTable(bool $query = false): Illuminate\Support\Collection 从提供的 Eloquent 模型检索服务器端 DataTables 数据。

参数

  • $query (bool，可选)：是否返回查询构建器而不是执行查询。默认为 false。

示例

以下是如何在控制器中利用这些方法的示例

use App\Models\YourModel;
use ChandraHemant\ServerSideDatatable\DynamicModelDataTableHelper;

class YourController extends Controller
{
    public function index()
    {
        $dynamicConditions = [
            [
                'method' => 'whereColumn',
                'args' => ['column1', '>=', 'column2'],
                'condition' => 'loss'
            ],
            [
                'method' => 'whereColumn',
                'args' => ['column1', '<', 'column2'],
                'condition' => 'profit'
            ],
            [
                'method' => 'whereRaw',
                'args' => ['YEAR(column3) = ?', session()->get('financialYear')]
            ],
            [
                'method' => 'whereIn',
                'args' => ['column4', session()->get('values')]
            ],
            [
                'method' => 'where',
                'args' => ['column6', 0]
            ],
            [
                'method' => 'whereHas',
                'args' => ['relation1', function ($query1) {
                    $query1->where('column1', '=', 4);
                }]
            ],
            [
                'method' => 'whereRelation',
                'parentMethod' => 'whereHas',
                'childMethod' => 'whereIn',
                'args' => ['column', 'value'],
                'relation' => 'relationship_method'
            ],
            [
                'method' => 'nestedCondition',
                'parentMethod' => 'where',
                'nestedMethod' => [
                    [
                        'childMethod' => 'where',
                        [
                            'method' => 'where',
                            'args' => ['column1', '=', 5]
                        ],
                        [
                            'method' => 'whereIn',
                            'args' => ['column2', [1, 4, 7]]
                        ],
                    ],
                    [
                        'childMethod' => 'orWhere',
                        [
                            'method' => 'where',
                            'args' => ['column1', '!=', 5]
                        ],
                        [
                            'method' => 'whereIn',
                            'args' => ['column3', [1, 4, 7]]
                        ],
                    ],
                ],
            ], 
            [
                'method' => 'nestedRelationCondition',
                'parentMethod' => 'whereHas',
                'relation' => 'relationship_method',
                'args' => [['column1', $user->id], ['column2', $statusId]],
                'nestedMethod' => [
                    [
                        'childMethod' => 'whereDoesntHave',
                        'relation' => 'relationship_method1',
                        'nestedConditions' => [
                            [
                                'method' => 'where',
                                'args' => ['log_request_id', $requestId]
                            ],
                            [
                                'method' => 'where',
                                'args' => [['column1', $user->id], ['column2', $statusId]]
                            ]
                        ]
                    ]
                ]
            ],
            [
                'method' => 'select',
                'args' => ['column1','column2','column3','column4','column5','column6','column7','column8','column9'],
                'relation' => ['relation1','relation2','relation3','relation4','relation5','relation6','relation7','relation8','relation9'],
            ],
            [
                'method' => 'orderBy',
                'args' => ['column1','column2','column3','column4','column5','column6','column7','column8','column9']
            ]
        ];

        // Define search value, columns, and relationships
        $searchColumns = ['column1','column2','column3','column4','column5','column6','column7','column8','column9'];

        $searchRelationships = [
            'relation1' => ['column1'],
            'relation2' => ['column2'],
            'relation3' => ['column3'],
            'relation4' => ['column4'],
            'relation5' => ['column5'],
            'relation6' => ['column6'],
            'relation7' => ['column7'],
            'relation8' => ['column8'],
            'relation9' => ['column9'],
        ];
        $helper = new DynamicModelDataTableHelper(
            eloquentModel: $modelInstance,
            dynamicConditions: $dynamicConditions,
            searchColumns: $searchColumns,
            searchRelationships: $searchRelationships
        );

        $data = $helper->getServerSideDataTable();

        return view('your_view', compact('data'));
    }
}

结论

本指南提供了如何在 Laravel 应用程序中使用 DynamicModelDataTableHelper 类的基本概述。按照这些说明，您可以轻松实现具有动态条件、分页和搜索功能的服务器端数据表。

DataTableHelper 类

DataTableHelper 类是一个 PHP 工具，旨在增强 Laravel 应用程序中 DataTables 的服务器端数据检索。DataTables 是一个流行的 JavaScript 库，用于在 Web 应用程序中创建交互式和动态表格。此类提供了一组方法来构建和执行灵活的数据库查询，支持诸如表连接、列选择、搜索过滤、排序和分页等功能。

方法

getServerSideDataTable($column, $join = array())

从指定的数据库表检索数据，可选择连接多个表。此方法根据提供的参数应用搜索过滤、排序和分页。

countFilteredServerSideDataTable($column, $join = array())

根据指定的标准计算过滤行的数量。在分页之前确定总计数非常有用。

getDataWithJoinTables($column, $join = array())

可选择连接多个表检索数据。此方法根据提供的参数应用排序。

私有方法

buildQuery($column, $join)

使用指定的表构建初始查询，并应用WHERE条件、选择列和连接表。

applyWhereConditions($query, $column)

根据提供的参数将WHERE条件应用于查询。

applySelectColumns($query, $selectColumns)

根据提供的参数将SELECT列应用于查询。

applyJoinTables($query, $join)

根据提供的连接条件和类型将JOIN子句应用于查询。

applySearchFilter($query, $selectColumns)

根据用户提供的搜索值对查询应用搜索过滤。

applyOrdering($query, $column)

根据用户提供的排序参数或预定义排序对查询应用排序。

applyPagination($query)

根据用户提供的起始和长度参数对查询应用分页。

使用示例

/**
 * Retrieve data from a database table with the option to join multiple tables.
 *
 * @param  array|string|null  $table
 *   The name of the database table to retrieve data from, or an array of table names if joining multiple tables.
 *   If not specified, the default table name will be used.
 *
 * @param  array|string|null  $join
 *   An array defining the join conditions and table aliases for each table to join.
 *   The array should be structured as follows:
 *     - 'tables': An array of table names to join in the query, in the order that they should be joined.
 *     - 'fields': An array of field names to select from each table in the query, in the same order as the 'tables' array.
 *     - 'joinType': An array of join types to use for each join, in the same order as the 'tables' array.
 *   If not specified, no join will be performed.
 *   Example:
*       $join = array(
*           'tables'=> array(
*               array(
*                   'table_1', 'table_2'
*               ),
*           ),
*           'fields'=>array(
*               array(
*                   'column_table_1', 'column_table_2'
*               ),
*           ),
*           'join_type'=>array(
*               'inner',
*           ),
*       );
 *
 * @param  array  $column
 *   An array specifying the columns, ordering, and filtering conditions for the query.
 *   Example:
 *
 *       $column = array(
 *           'table'=> 'table_1',
 *           'order'=> array(
 *               array('table_1', 'column_1_table_1'),
 *               array('table_1', 'column_2_table_1'),
 *               array('table_1', 'column_3_table_1'),
 *               array('table_2', 'column_1_table_2')
 *           ),
 *           'select'=>array(
 *               array('table_1', 'column_1_table_1'),
 *               array('table_1', 'column_2_table_1'),
 *               array('table_1', 'column_3_table_1'),
 *               array('table_2', 'column_1_table_2')
 *           ),
 *           'where'=>array(
 *               array('column' => 'table_1.column_name', 'operator' => '=', 'value' => '1')
 *           ),
 *           'orderBy'=>array(
 *               array('column' => 'column_.column_name', 'direction' => 'DESC')
 *           ),
 *       );
 *
 * @return \Illuminate\Support\Collection
 *   The result of the database query.
 */

// Specify columns, ordering, and filtering conditions
$column = array(
    'table'=> 'products',
    'order'=> array(
        array('products', 'prod_id'),
        array('products', 'prod_name'),
        array('products', 'prod_type'),
        array('products', 'prod_descr'),
        array('products', 'prod_total_price'),
        array('products', 'prod_nsv'),
        array('productCategory', 'cat_name')
    ),
    'select'=>array(
        array('products', 'prod_id'),
        array('products', 'prod_name'),
        array('products', 'prod_type'),
        array('products', 'prod_descr'),
        array('products', 'prod_total_price'),
        array('products', 'prod_nsv'),
        array('productCategory', 'cat_name')
    ),
    'where'=>array(
        array('column' => 'products.mf_id', 'operator' => '=', 'value' => '1')
    ),
    'orderBy'=>array(
        array('column' => 'products.prod_id', 'direction' => 'DESC')
    ),
);
// Specify join conditions and types
$join = array(
    'tables'=> array(
        array('productCategory', 'products'),
    ),
    'fields'=>array(
        array('cat_id', 'cat_id'),   
    ),
    'joinType'=>array(
        'left', 
    ),
);

$list = DataTableHelper::getServerSideDataTable($column, $join);
$count = DataTableHelper::countFilteredServerSideDataTable($column, $join);

/**
 * Construct the output array for a server-side DataTable response.
 *
 * @var array $output
 *   An associative array containing the following keys:
 *     - 'draw': The DataTable draw counter to control asynchronous requests.
 *                Example: 'draw' => request()->input('draw')
 *     - 'recordsTotal': The total number of records in the entire dataset, regardless of filtering.
 *                Example: 'recordsTotal' => sizeof($list)
 *     - 'recordsFiltered': The total number of records after filtering, considering the provided column and join configurations.
 *                Example: 'recordsFiltered' => $count
 *     - 'data': The actual data to be displayed in the DataTable.
 *                Example: 'data' => $data
 */

$data = array();
foreach ($list as $val) {
    $row = array();
                    
    $row[] = '#'.$val->prod_id;			
    $row[] = $val->prod_name;			
    $row[] = $val->prod_type;						
    $row[] = $val->prod_descr;						
    $row[] = $val->prod_total_price;							
    $row[] = $val->prod_nsv;							
    $row[] = $val->cat_name;	
        
    $data[] = $row;
}

$output = array(
    "draw"            => request()->input('draw'),
    "recordsTotal"    => sizeof($list),
    "recordsFiltered" => $count,
    "data"            => $data,
);

echo json_encode($output);

Eloquent模型DataTable辅助器

这个PHP类ModelDataTableHelper提供了从Eloquent模型中检索服务器端DataTable数据的方法。它提供了排序、过滤和连接多个表的选项，是Laravel应用程序中与数据库表交互的多功能工具。

getServerSideDataTable

从Eloquent模型检索服务器端DataTable数据。

Eloquent模型DataTable辅助器的使用示例

/**
 * Retrieve server-side DataTables data from an Eloquent model.
 *
 * @param  \Illuminate\Database\Eloquent\Model  $eloquentModel
 *   The Eloquent model to query for data.
 *
 * @param  array  $column
 *   An array specifying the columns, ordering, and filtering conditions for the query.
 *   Example:
 *   $column = [
 *       'orderBy' => [
 *           ['column' => 'model_column_1', 'direction' => 'DESC'],
 *           ['column' => 'model_relation_function.relation_model_column_1', 'direction' => 'ASC'],
 *       ],
 *       'order' => [
 *           ['model_column_1'],
 *           ['model_column_2'],
 *           ['model_column_3'],
 *           ['model_relation_function.relation_model_column_1'],
 *       ],
 *       'select' => [
 *           ['model_column_1', 'alias_name_1'],
 *           ['model_column_2', 'alias_name_2'],
 *           ['model_column_3', 'alias_name_3'],
 *           ['model_relation_function.relation_model_column_1', 'alias_name_4'],
 *       ],
 *       'where' => [
 *           ['column' => 'model_column_4', 'operator' => '=', 'value' => '1'],
 *           ['column' => 'model_relation_function.relation_model_column_1', 'operator' => '=', 'value' => '1', 'encrypted' => true],
 *           ['column' => 'YEAR(model_column_5) = ?', 'operator' => '=', 'value' => '2024', 'isRaw'=>true],
 *           ['column' => 'model_relation_function_1.relation_model_column_1', 'operator' => '!=', 'value' => '["5,"6"]', 'isArray'=>true],
 *           ['column' => 'model_column_6', 'operator' => '>=', 'value' => 'model_column_7', 'isColumn' => true],
 *       ],
 *   ];
 *
 * @return \Illuminate\Support\Collection
 *   The result of the server-side DataTables query.
 */

$column = [
    'orderBy' => [
        ['column' => 'prod_id', 'direction' => 'DESC'],
        ['column' => 'productCategory.cat_name', 'direction' => 'ASC'],
    ],
    'order' => [
        ['prod_id'],
        ['prod_name'],
        ['prod_type'],
        ['prod_descr'],
        ['prod_total_price'],
        ['prod_nsv'],
        ['productCategory.cat_name'],
    ],
    'select' => [
        ['prod_id', 'id'],
        ['prod_name', 'name'],
        ['prod_type', 'type'],
        ['prod_descr', 'description'],
        ['prod_total_price', 'total_price'],
        ['prod_nsv', 'nsv'],
        ['basic_price', 'price'],
        ['productCategory.cat_name', 'category_name'],
    ],
    'with' => [
        ['relation'=>'audit_employee'],
        ['relation'=>'audits', 'nested' => [ 'relation' => 'user_detail', 'selectColumn' => ['emp_id', 'emp_name']]],
    ],
    'where' => [
        ['column' => 'productCategory.mf_id', 'operator' => '=', 'value' => 'c4ca4238a0b923820dcc509a6f75849b', 'encrypted' => true],
        ['column' => 'YEAR(we_from_pre) = ?', 'operator' => '=', 'value' => session()->get('financialYear'), 'isRaw'=>true],
        ['column' => 'product_category.cat_type', 'operator' => '!=', 'value' => '["5,"6"]', 'isArray'=>true],
        ['column' => 'prod_nsv', 'operator' => '=', 'value' => 'basic_price', 'isColumn' => true],
    ],
];

$list = ModelDataTableHelper::getServerSideDataTable(new Product(), $column);

如何使用

  1. 安装：确保DataTableHelper类已包含在你的Laravel项目中。
  2. 初始化：创建DataTableHelper类的实例。
  3. 数据检索：使用指定的列、连接条件和其它参数调用getServerSideDataTable方法，以检索分页和过滤后的数据。
  4. 过滤行计数：可选地，使用countFilteredServerSideDataTable方法在分页之前确定过滤行的总数。
  5. 自定义：通过修改方法或扩展其功能来调整类以满足你的特定用例。

通过利用DataTableHelper和ModelDataTableHelper类，你可以轻松地将服务器端DataTable功能集成到Laravel应用程序中，提供一种用户友好且高效的方式来处理表格式的大数据集。

注意

在$column['order']数组和“表头”之间保持顺序和长度的连贯性对于确保DataTable中准确的预期列排序至关重要。

此连接基于$column['order']数组和相关表的表头具有相同顺序和长度的假设。该$column['order']数组用于指定DataTable中列的默认排序顺序。该数组中的每个元素代表一个列，包括有关表和列名称以及所需排序方向（例如，升序或降序）的信息。

为了使此连接无缝工作，确保$column['order']数组中元素的数量和长度与相应表的表头相匹配至关重要。DataTable将$column['order']数组中元素的数量和长度解释为确定页面加载时的初始列排序。如果$column['order']数组中元素的数量和长度与表的表头不匹配，则可能会出现意外的排序行为。