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

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

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

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

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

あわせて読みたい

ココナラと似てるサイト比較11選！【2023年最新版】 この記事ではココナラに似てる副業サイトを11こ紹介しています。 今すぐスキルをお金にするなら、記事で気になったサービスに登録してみましょう。 本記事のゴール ココ…

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

あわせて読みたい

転職におすすめなサイト11選【リモート・フリーランスも対象】 転職におすすめなサイトが知りたいなぁ… 転職エージェントによって相性があります！ この記事では、フリーランスの方でも利用できる転職におすすめなサイトを紹介しま…

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

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

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

ゴール

↓

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

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

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

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

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

よって①と②により、

となります。

サンプルコード

<?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しグループ化させる

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

ゴール

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

ここから、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しグループ化させる

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

ゴール

↓

考え方としては、
・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しグループ化させる

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

ゴール

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

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時を取り出すことができます。

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

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

あわせて読みたい

ココナラと似てるサイト比較11選！【2023年最新版】 この記事ではココナラに似てる副業サイトを11こ紹介しています。 今すぐスキルをお金にするなら、記事で気になったサービスに登録してみましょう。 本記事のゴール ココ…

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

あわせて読みたい

転職におすすめなサイト11選【リモート・フリーランスも対象】 転職におすすめなサイトが知りたいなぁ… 転職エージェントによって相性があります！ この記事では、フリーランスの方でも利用できる転職におすすめなサイトを紹介しま…

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