laravel mysql concat_如何将许多语句mysql转换为laravel雄辩？

您应该将查询作为Mysql Procedure并针对您的数据库运行它，我在下面对您的查询做了些微调整，

DROP PROCEDURE IF EXISTS searchitems;

DELIMITER $$

CREATE PROCEDURE searchitems()

BEGIN

SET @@group_concat_max_len = 75000;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'max(CASE WHEN location_code = ''',

location_code,

''' THEN coalesce(quantity, 0) END) AS `',

location_code, '`'

)

) INTO @sql

FROM

item_details;

SET @query := CONCAT('SELECT item_number,SUM(quantity) as "total_quantity", ', @sql, '

FROM item_details

GROUP BY item_number');

PREPARE stmt FROM @query;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET @@group_concat_max_len = 1024;

END $$

DELIMITER ;

然后使用Query Builder Approach这样从Laravel控制器调用过程，

$queryResult = $db->prepare('call searchitems()');

$queryResult->execute();

$results = $queryResult->fetchAll(PDOConnection::FETCH_ASSOC);

$queryResult->closeCursor();

然后将结果集转换为Laravel集合，然后您可以像这样轻松进行分页，

$results_collection = collect($results);

$currentPage = LengthAwarePaginator::resolveCurrentPage();

$perPage = 20;

$currentPageSearchResults = $results_collection->slice(($currentPage - 1) * $perPage, $perPage)->all();

$paginatedSearchResults = new LengthAwarePaginator($currentPageSearchResults, count($results_collection), $perPage);

$paginatedSearchResults->setPath($request->url());

$paginatedSearchResults->appends($request->except(['page']));

然后像这样返回视图

return view('yourview')

->with('results',$paginatedSearchResults);

//or if ajax call

$viewData =  view('yourview')

->with('results',$paginatedSearchResults)

->render();

$response = [

"Status" => "Success",

"Content" =>   $viewData

];

return response()->json($response);

在刀片视图模板中，您可以在foreach循环中访问数据并在表或列表视图中呈现，以显示分页，您可以像这样

//to render pagination is front end

<?php echo $results->render(); ?>

但是，我建议您缓存数据，以避免重复调用过程并提高性能，因为如果您有数百万个数据，这种构建分页的方式会减慢您的应用程序的速度，如果您需要实现Ajax分页