代码之家 › 专栏 › 技术社区 › Success Man

如何将多个语句mysql转换为laravel eloguent?

laravel-5.6 eloquent laravel-5 laravel php

Success Man · 技术社区 · 6 年前

mysql查询如下:

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;

我想把它转换成拉拉维尔口才,但我很困惑。因为有很多说法。存在 PREPARE , EXECUTE , SET , DEALLOCATE 等等,你可以看到上面的查询

我怎样才能把它转换成拉拉维尔雄辩?

3 回复 | 直到 6 年前

Jonas Staudenmeir 6 年前

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

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

Mohamed Akram 6 年前

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 ;

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

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

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

this article

Luke G. 6 年前

How to create select dynamic fields from a table in mysql?