我想基于两个组生成数据。即当年的月份组和然后基于中产品的现有产品id计算的组id
我试过这个代码
$tahunx = '2021';
$barangmasuk=BarangMasuk::groupBy('id_barang','months')->select([
'barang_masuk.id_barang',
DB::raw("DATE_FORMAT(tgl_masuk,'%M %Y') as months", '=',$tahunx),
DB::raw('sum(jml_bm) as jumlah')
])->get();
dd($barangmasuk);
我想创建看起来像这个的数据
我使用的有表1和表2
我已经给出了,但这是结果,对于按id_barang分组可以,但按月份分组没有得到的结果
Collection {#862 ▼
#items: array:7 [▼
0 => BarangMasuk {#854 ▼
#table: "barang_masuk"
#fillable: array:4 [▶]
#connection: "mysql"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:3 [▼
"id_barang" => 1
"months" => "April 2021"
"jumlah" => "100"
]
#original: array:3 [▼
"id_barang" => 1
"months" => "April 2021"
"jumlah" => "100"
]
#changes: []
#casts: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: []
#touches: []
+timestamps: true
#hidden: []
#visible: []
#guarded: array:1 [▶]
}
1 => BarangMasuk {#855 ▶}
2 => BarangMasuk {#856 ▶}
3 => BarangMasuk {#857 ▶}
4 => BarangMasuk {#858 ▶}
5 => BarangMasuk {#859 ▶}
6 => BarangMasuk {#860 ▶}
]
}
结果是双id和月份数据
这是我使用的表格的一个例子
Tabel barang
+-----+-------------+---------+
| id | nama_barang | stok |
+-----+-------------+---------+
| 1 | Sayur | 10 |
| 2 | daging | 50 |
| 3 | Buah | 20 |
+-----+-------------+---------+
主要参数表
+-----+-------------+-----------+---------+
| id | tgl_masuk | id_barang | jml_bm |
+-----+-------------+-----------+---------+
| 1 | 25-05-2021 | 1 | 200 |
| 2 | 25-05-2021 | 2 | 100 |
| 3 | 25-05-2021 | 1 | 100 |
| 4 | 25-05-2021 | 1 | 200 |
| 5 | 25-05-2021 | 2 | 100 |
| 6 | 25-05-2021 | 1 | 100 |
| 7 | 25-04-2021 | 1 | 300 |
| 8 | 25-04-2021 | 2 | 200 |
| 9 | 25-04-2021 | 2 | 100 |
| 10 | 25-04-2021 | 1 | 200 |
| 11 | 25-04-2021 | 3 | 100 |
| 12 | 25-04-2021 | 3 | 100 |
+-----+-------------+-----------+---------+
为了得到这样的结果
+-------------+------------+-----------+
| tgl_masuk | id_barang | sum(jml_bm|
+-------------+------------+-----------+
| April-2021 | 1 | 500 |
| April-2021 | 2 | 300 |
| April-2021 | 3 | 200 |
| Mei-2021 | 1 | 600 |
| Mei-2021 | 2 | 200 |
+-------------+------------+-----------+
在普通MySQL查询中,您可以执行以下操作:
SELECT DATE_FORMAT(tgl_masuk,'%M-%Y') AS 'DY', id_barang, SUM(jml_bm) AS 'Jumlah'
FROM barang_masuk
WHERE YEAR(tgl_masuk)='2021'
GROUP BY DY, id_barang
ORDER BY DY, id_barang;
尽管我不知道如何将这个查询转换为laravel/雄辩。
演示小提琴