php - 如何在 laravel eloquent 控制器上正确应用 foreach 以进行表格转置

Question

我想创建显示转置表的控制器，其中行转换为列。

这是关于将行转换为列的参考

参考代码：

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

我的查询是控制器目标：

select 
  nilai_t1.nim,
  pendaftar.nama,
  sum(case when nilai_t1.id_sk1 = 11 then nilai_t1.nilai else 0 end) as 'Tanggung Jawab',
  sum(case when nilai_t1.id_sk1 = 12 then nilai_t1.nilai else 0 end) as 'Keaktifan',
  sum(case when nilai_t1.id_sk1 = 13 then nilai_t1.nilai else 0 end) as 'Teamwork',
  sum(case when nilai_t1.id_sk1 = 21 then nilai_t1.nilai else 0 end) as 'Wawancara',
  sum(case when nilai_t1.id_sk1 = 31 then nilai_t1.nilai else 0 end) as 'Tes Bakat'
from nilai_t1
inner join sub_kriteria_t1
  on nilai_t1.id_sk1 = sub_kriteria_t1.id_sk1
inner join kriteria_t1
  on sub_kriteria_t1.id_k1 = kriteria_t1.id_k1
inner join peserta_t1
    on nilai_t1.nim = peserta_t1.nim
inner join pendaftar
    on peserta_t1.nim = pendaftar.nim
group by nilai_t1.nim;

Before:
+----+------------+--------+-------+
| id | nim        | id_sk1 | nilai |
+----+------------+--------+-------+
|  1 | 181011**** |     11 |    30 |
|  2 | 181011**** |     12 |    30 |
|  3 | 181011**** |     13 |    25 |
|  4 | 181011**** |     21 |    72 |
|  5 | 181011**** |     31 |    57 |
|  6 | 181052**** |     11 |    20 |
|  7 | 181052**** |     12 |    20 |
|  8 | 181052**** |     13 |    10 |
|  9 | 181052**** |     21 |    75 |
| 10 | 181052**** |     31 |    57 |
+----+------------+--------+-------+

After:
+------------+------------------------+----+----+----+----+----+
| nim        | nama                   | 11 | 12 | 13 | 21 | 31 |
+------------+------------------------+----+----+----+----+----+
| 181011**** | Annisa indra           | 30 | 30 | 25 | 72 | 57 |
| 181052**** | Muhammad fakhri naufal | 20 | 20 | 10 | 75 | 57 |
+------------+------------------------+----+----+----+----+----+

这是我的控制器代码之一：

public function calculate(){
        $sk_pluck = $sk = SubKriteriaTahap1::
        join('kriteria_t1', 'sub_kriteria_t1.id_k1', '=', 'kriteria_t1.id_k1')
        ->pluck(
            'sub_kriteria_t1.id_sk1');

        $sk_pluck->all();

        //$kriteria = KriteriaTahap1::all();
        $transpose=[];
        foreach ($sk_pluck as $sk1){
          $transpose = 'sum(case when nilai_t1.id_sk1 =' .$sk1.'  then nilai_t1.nilai else 0 end) AS \'Table\'';
        }

        $trans_array = array($transpose);

        $penilaian1 = PenilaianTahap1::select('nilai_t1.nim',
        'pendaftar.nama', DB::raw(implode(',', $trans_array)))
        ->join('peserta_t1', 'nilai_t1.nim', '=', 'peserta_t1.nim')
        ->join('pendaftar', 'peserta_t1.nim', '=', 'pendaftar.nim')
        ->join('sub_kriteria_t1', 'nilai_t1.id_sk1', '=', 'sub_kriteria_t1.id_sk1')
        ->join('kriteria_t1', 'sub_kriteria_t1.id_k1', '=', 'kriteria_t1.id_k1')
        ->groupBy('nilai_t1.nim')
        ->get([
            'nilai_t1.nim',
            'nilai_t1.nilai',
            'nilai_t1.id_sk1'
        ]);


        $response = [
            'message' => 'id',
            'data' => $trans_array
        ];
        return response()->json($response, Response::HTTP_OK);
    }

但问题是，foreach 循环不起作用。它只显示一个标准而不是许多可用的标准。这是基于 REST API 的当前结果：

{"message":"id","data":["sum(case when nilai_t1.id_sk1 =31  then nilai_t1.nilai else 0 end) AS 'Table'"]}

但是$sk_pluck确实显示了许多可用的标准 ID：

{"message":"id","data":[11,12,13,21,31]}

因为这些问题，控制器只产生具有单一标准的查询：

{"message":"id","data":[{"nim":1810112048,"nama":"Annisa indra","Table":57},{"nim":1810522038,"nama":"Muhammad fakhri naufal","Table":57}]}

那么如何在我的控制器上正确使用 foreach 以便SUM表转置（将行转换为列）的查询有效？或者有什么解决办法？

标签： phpmysqllaravelforeachpivot-table