How can I convert many statement mysql to laravel

Question

Mysql query like this :

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when location_code = ''',
      location_code ,
      ''' then quantity end),0) AS `',
      location_code , '`'
    )
  ) INTO @sql
FROM
  item_details;
SET @sql = CONCAT('SELECT item_number,SUM(quantity) as "total_quantity", ', @sql, ' 
                  FROM item_details
                   GROUP BY item_number');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

I want to convert it to laravel eloquent, but i'm confused. Because there exist many statement. There exist PREPARE, EXECUTE, SET, DEALLOCATE etc. You can see query above

How can I convert it to laravel eloquent?

Answer 1

You should make your query as a Mysql Procedure and run it against your database, I did slight adjustment to your query below,

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 ;

then call your procedure from Laravel controller using Query Builder Approach like this,

$queryResult = $db->prepare('call searchitems()'); 
$queryResult->execute(); 
$results = $queryResult->fetchAll(PDOConnection::FETCH_ASSOC); 
$queryResult->closeCursor(); 

then convert your result set into Laravel collection, then you can make pagination easily like this,

 $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']));

then return to view like this

    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);

and in your blade view template, you can access the data in a foreach loop and render in table or list view, to display the pagination, you can do like this

//to render pagination is front end
<div class="row">
    <div class="col-md-offset-4">
        <?php echo $results->render(); ?>
    </div>
</div>  

However I advice you caching the data to avoid repeated calls to procedure and increase the performance, since this way of building pagination will slow down your application if you have millions of data, if you need implementing Ajax pagination, you may refer to this article

Answer 2

It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
    ->selectRaw($sql)
    ->groupBy('item_number')
    ->get();

Answer 3

This appears to be a duplicate (How to create select dynamic fields from a table in mysql?).

You asked for and received assistance on the other thread, but are asking here for how to actually implement the answer they gave there. (And have been rather combative about asking, I might add.)

It's generally best to ask for clarifications on the code you got as an answer in the question where it was given.