如何将许多语句mysql转换为laravel雄辩? [英] 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，但是我很困惑.因为存在很多陈述.存在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?

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雄辩?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！