こんにちは！booost technologies Webエンジニアのramcursweです。
最近の業務でコードを確認していると、groupByとsumの両メソッドを使っている箇所があったのですが、そこで考慮する必要があった注意点に関し、備忘および共有としてまとめたいと思います。
前提
- PHPのバージョン：8.1.27
- Laravelのバージョン：10.48.12
- MySQLのバージョン：8.0.30
- テーブル：確認用に以下のitemsテーブルを準備
目的
- 特定のグループごとに合計値を算出
今回の例だとcategory・partごとにpriceの合計を求める。 - 全グループを合わせた総計値を算出
解説
まずは、NG・OK例を示します（Eloquentを使用）。
NG
Item::select('category', 'part', DB::raw('SUM(price)'))
->groupBy('category', 'part')
->sum('price'); // 合計値：1600、'price'の総計値と異なる
OK
Item::select('category', 'part', DB::raw('SUM(price) as total_price'))
->groupBy('category', 'part')
->get()
->sum('total_price'); // 合計値：2620、'price'の総計値と一致
結論としては、sumメソッドで処理する前に、getメソッドを使う必要があります。
上記のNG例のように、直接sumメソッドをチェーンするとグルーピング結果を正しく反映できず、意図しない値になります。
getメソッドを使えば以下の通り、グルーピング結果がCollectionとして取得されます。
$groupByCollection = Item::select('category', 'part', DB::raw('SUM(price) as group_price'))
->groupBy('category', 'part')
->get();
Illuminate\Database\Eloquent\Collection {
all: [
App\Models\Item {
category: "weapon",
part: "arm",
group_price: "1600",
},
App\Models\Item {
category: "armor",
part: "arm",
group_price: "500",
},
App\Models\Item {
category: "consumables",
part: "other",
group_price: "20",
},
App\Models\Item {
category: "armor",
part: "head",
group_price: "500",
},
],
}
また、生のSQL部分で集計結果のエイリアスを指定し、それをsumメソッドの引数にします。
エイリアスを指定しないと集計結果名がSUM(price)になる一方、以下の例のようにsumメソッドの引数priceと一致せず、合計値が0になってしまいます。
NG
Item::select('category', 'part', DB::raw('SUM(price)'))
->groupBy('category', 'part')
->get()
->sum('price'); // 合計値：0、一応sum('SUM(price)')とすればOKだが分かりにくい
OK
Item::select('category', 'part', DB::raw('SUM(price) as total_price'))
->groupBy('category', 'part')
->get()
->sum('total_price'); // 合計値：2620、'price'の総計値と一致
なお、今回の例において、単に総計値を取得するだけならば、そもそもgroupByメソッドは不要であり、以下のようにEloquentのsumメソッドで簡単にできます。
Item::sum('price'); // 合計値：2620
もし、各グループごとの集計値を後続処理で使ったり、共通化して他の処理でも利用したい場合は、前述の方法でCollectionを取得した後、用途に合わせて使用すると便利です。
// グループごとの集計値をまとめたCollectionを、各メソッドで使い回す
$groupByCollection = Item::select('category', 'part', DB::raw('SUM(price) as group_price'))
->groupBy('category', 'part')
->get();
function getTotalPrice($groupByCollection) {
$groupByCollection->sum('group_price'); // 合計値：2620
}
function getGroupCount($groupByCollection) {
$groupByCollection->count(); // 件数：4
}
補足
ちなみに、他の集計メソッドでも同様の事象が発生します。
count()
NG
Item::select('category', 'part')
->groupBy('category', 'part')
->count(); // 件数：2、Collectionに含まれるグループ数と異なる
OK
Item::select('category', 'part')
->groupBy('category', 'part')
->get()
->count(); // 件数：4、Collectionに含まれるグループ数と一致
max()
NG
Item::select('category', 'part', DB::raw('SUM(price)'))
->groupBy('category', 'part')
->max('price'); // 最大値：1000、Collectionに含まれる集計値の最大値と異なる
OK
Item::select('category', 'part', DB::raw('SUM(price) as group_price'))
->groupBy('category', 'part')
->get()
->max('group_price'); // 最大値：1600、Collectionに含まれる集計値の最大値と一致
min()
NG
Item::select('category', 'part', DB::raw('SUM(price)'))
->groupBy('category', 'part')
->min('price'); // 最小値：600、Collectionに含まれる集計値の最小値と異なる
OK
Item::select('category', 'part', DB::raw('SUM(price) as group_price'))
->groupBy('category', 'part')
->get()
->min('group_price'); // 最小値：20、Collectionに含まれる集計値の最小値と一致
avg()
NG
Item::select('category', 'part', DB::raw('SUM(price)'))
->groupBy('category', 'part')
->avg('price'); // 平均値：800.0000、Collectionに含まれる集計値の平均値と異なる
OK
Item::select('category', 'part', DB::raw('SUM(price) as group_price'))
->groupBy('category', 'part')
->get()
->avg('group_price'); // 平均値：655、Collectionに含まれる集計値の平均値と一致
まとめ
groupByと集計メソッドを組み合わせて使う際は、getメソッドで一旦グルーピング結果を取得しましょう！
また、生のSQL部分で集計結果のエイリアスを指定するのも忘れずに！
※getの処理はデータ数が多くなるとパフォーマンス問題が生じるので、その場合はSQL側で処理する等の検討が必要です。
宣伝
booost technologiesでは、Webエンジニアを積極採用中です。
もしご興味をお持ちいただけましたら、ぜひお気軽にお声がけください。
Discussion