副業におすすめなサイトを見る→

【Laravel】groupByで複数カラム・曜日別・日付別・時間別でグループ化させる

Laravelグループ化

アプリリリースのお知らせ

予定を探すアプリyoteipickerをリリースしました。

アプリの利用用途:暇だから何か予定入れたいけど今週の土日は何しようかな〜?ってときに使えるアプリです。

Laravelで複数カラム・曜日別・日付別・時間別でグループ化させるにはどうすればいいんだろう?

この記事では上記の悩みを解決します。

それではさっそく解説していきます。

>>ココナラと似てるおすすめの副業サイトを確認する

>>リモートワークもあるおすすめの転職サイトを確認する

休日で空いた時間の暇つぶしを探せるアプリを公開しています。

Contents

複数カラムをgroupByでグループ化させる

複数カラムをグループ化させます。

ゴール

post_iduser_idpost_name
11カレー
22ちゃんこ
33チャーハン
41オムライス
52ちゃんこ

user_idpost_name
1オムライス
1カレー
2ちゃんこ
3チャーハン

複数のカラムをグループ化させる時の注意点として、

user_idpost_name
1オムライス,カレー
2ちゃんこ
3チャーハン

のようにできると考えてしまうことです。

post_nameにオムライス、カレーのように複数の項目を入れることはできません。

user_idとpost_nameでグループ化させているので、
①user_id→1,1,2,2,3でグループ化
=>1,2,3
②post_name→オムライス、カレー、ちゃんこ、ちゃんこ、チャーハンでグループ化
=>オムライス、カレー、ちゃんこ、チャーハン

ただ、オムライスはuser_id→1、カレーはuser_id→1なので、別々の項目としてデータを取得します。

よって①と②により、

user_idpost_name
1オムライス
1カレー
2ちゃんこ
3チャーハン

となります。

サンプルコード

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Post extends Model
{
    public function getResult()
    {
      // user_id,post_nameでグループ化
      return DB::table('posts')
              ->select('user_id', 'post_name')
              ->groupBy('user_id', 'post_name')
              ->get();
    }
}

以下はエラーになってしまう例です。

return DB::table('posts')
   ->select('user_id', 'post_name')
   ->groupBy('user_id')
   ->get();
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tests.posts.post_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `user_id`, `post_name` from `posts` group by `user_id`)

冒頭に解説した、user_idでまとめようとすると、post_nameが一部の項目で複数になってしまうのでエラー起きます。

“GROUP BY” で指定していないカラムが “SELECT” に入っているからです。

曜日別でgroupByしグループ化させる

曜日別にグループ化させます。

ゴール

曜日合計
200
300
500

下準備として以下のようなデータを用意します。

post_idamountcreated_at
11002021-06-01 12:23:07
22002021-06-15 15:23:23
33002021-06-30 16:21:12
44002021-07-21 19:23:32
52002021-07-27 15:04:56

ここから、created_atの曜日をグループ化させて、amountの合計値を算出する。

ちなみに 5つの日付は火曜か水曜になっています。
なので完成は、以下となります。

曜日合計
合計値
合計値

曜日でグループ化させるために、WEEKDAY関数を使う。
ビューで表示する際に、曜日の配列を用意する。
foreachと曜日の配列をもとに曜日を表示させる。

WEEKDAY関数の他にDAYOFWEEKがあります。違いは、WEEKDAYは月曜始まりで、DAYOFWEEKは日曜始まりな点です。

簡単に言えば、WEEKDAY関数は日付を月曜なら0,火曜なら1を返します。
DAYOFWEEKなら日曜なら0,月曜なら1を返します。

サンプルコード

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;←忘れずに記載する

class Post extends Model
{
    public function getAmountByHour()
    {
      return DB::table('posts')
              ->selectRaw('WEEKDAY(created_at) AS week')
              ->selectRaw('SUM(amount) AS total_amt')
              ->groupBy('week')
              ->get();
    }
}

selectRawはselect(DB::raw)の省略形。
WEEKDAY関数を使って曜日をグループ化させます。

WEEKDAY(created_at)で日付が対応する曜日となって返します。

例えば、2021-06-01は火曜日なので、->selectRaw(‘WEEKDAY(created_at) AS week’)は1を返します。

WEEKDAY関数やSUM,COUNT関数などを使う場合は、DB::rawが必要です。

SUM(amount)でamountの合計値を出しています。

最後にgroupBy(‘week’)でグループ化しています。
※AS weekでWEEKDAY(created_at)に別名をつけています。

日付別でgroupByしグループ化させる

日付ごとにグループ化させる方法です。

ゴール

post_idamountcreated_at
11002021-06-01 12:23:07
22002021-06-01 15:23:23
33002021-06-01 16:21:12
44002021-07-21 19:23:32

created_atamount
2021-06-01600
2021-07-21400

考え方としては、
・DATE_FORMATで2021-06-01を抽出する
・日付をグループ化させる
・amountをSUMで集計してデータを取得する

サンプルコード

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;←記述を忘れない

class Post extends Model
{
    public function getAmount()
    {
      return DB::table('posts')
              ->selectRaw('DATE_FORMAT(created_at, "%Y%m%d") AS date')
              ->selectRaw('SUM(amount) AS total_amount')
              ->groupBy('date')
              ->get();
    }
}

まず、DB::tableやDB::rawが使えるように、冒頭にuse Illuminate\Support\Facades\DB;を記述しましょう。

selectRawはselect(DB::raw)の省略形です。DATE_FORMAT関数を使う場合は、select(DB::raw(‘DATE_FORMAT()’))とも書けますが、selectRawで短縮できます。

ちなみにDATE_FORMATやSUM関数などはDB::raw(‘DATE_FORMAT()’)と DB::rawをセットで使う必要があります。

DATE_FORMAT(created_at, “%Y%m%d”)で2021-06-01 12:34:32を20210601に変換します。これで、日付をグループ化できます。
なぜなら、日付が一致していても時間が12時と14時で異なると別のグループと判別されてしまうからです。

SUM(amount)でamountを集計できます。

最後にgroupByで日付をグループ化させています。
DATE_FORMAT(created_at, “%Y%m%d”はAS dateと別名に置き換えているので、dateでグループ化できます。

日付別でgroupByしグループ化させる

時間ごとにグループ化させる方法です。

ゴール

時間合計
12:00〜12:59200
15:00〜15:59300
19:00〜19:59500

下準備として以下のデータを用意します。

post_idamountcreated_at
11002021-06-01 12:23:07
22002021-06-15 15:23:23
33002021-06-30 16:21:12
44002021-07-21 19:23:32
52002021-07-27 15:04:56

created_atの時間でグループ化させて合計値を出します。

HOUR関数を使用することで、時刻から時を取り出すことができます。

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;←忘れず記載する

class Post extends Model
{
    public function getAmountByHour()
    {
      return DB::table('posts')
              ->selectRaw('HOUR(created_at) AS time')
              ->selectRaw('SUM(amount) as total_amt')
              ->groupBy('time')
              ->get();
    }
}

selectRawはselect(DB::raw)の短縮系です。HOUR関数で2021-06-08 12:34:55
の12時を取り出すことができます。

あとは時間でグループ化させれば集計できます。

>>ココナラと似てるおすすめの副業サイトを確認する

>>リモートワークもあるおすすめの転職サイトを確認する

休日で空いた時間の暇つぶしを探せるアプリを公開しています。

スキルを売り買いするならココナラ

コメント

コメントする

CAPTCHA


Contents
閉じる