如何将许多语句 mysql 转换为 Laravel eloquent? [英] How can I convert many statement mysql to laravel eloquent?

问题描述

Mysql 查询如下:

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;

我想将其转换为 Laravel eloquent，但我很困惑.因为有很多说法.存在PREPARE、EXECUTE、SET、DEALLOCATE等，可以看上面的查询

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

如何将其转换为 Laravel eloquent?

How can I convert it to laravel eloquent?

推荐答案

主要是原始查询:

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

这篇关于如何将许多语句 mysql 转换为 Laravel eloquent?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！