【Laravelクエリビルダ】DATE_FORMATした日付でgroupByする方法

Laravelのクエリビルダで日付でグループ化させてデータを取得する方法について解説します。

Laravelのバージョンは6系です。

ブログで以下のような記事も書いています。

ゴール

今回のゴールはこんな感じです。
postsテーブルを日付でグループ化させて、amountを集計する

postsテーブル

↓

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

それではやっていこう

まずデータベースに必要な情報を入れましょう。

モデルにデータ取得のロジックを書き、コントローラーには変数をビューに渡し、ビューでforeachでデータを取得する流れです。

モデル(Post.php)

<?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でグループ化できます。

コントローラー側(postController.php)

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Post;←忘れない(バージョン6の場合)
// use App\Models\Post;バージョン8以降はこっち書く

class PostController extends Controller
{
    public function index()
    {
        $this->posts = new Post();
        $results = $this->posts->getAmount();

        return view('test.index', compact(
            'results',
        ));
    }
}

ビュー(index.blade.php)

<table>
    <thead>
      <tr>
        <th>日付</th>
        <th>合計金額</th>
      </tr>
    </thead>
    <tbody>
    @foreach ($results as $result)
      <tr>
        <td>{{ date('Y/m/d', strtotime($result->date)) }}</td>
        <td>{{ $result->total_amount }}</td>
      </tr>
    @endforeach
    </tbody>
</table>

ビューに渡された$resultsには取得したデータが格納されています。
なので、foreachでデータを取得できます。

$result->dateでもいいですが、20210601となるはずです。
これはちょっと気持ち悪いので、date関数とstrtotimeを使って、date('Y/m/d', strtotime($result->date))としています。

これで、20210601→2021/06/01になります。
ちなみに、date('Y年m月d日', strtotime($result->date))って書けば、2021年6月1日と表示されます。

実際の出力画面

groupByの注意点

今回は日付でグループ化させましたが、グループ化には注意点あります。

例えば、日付でグループ化させたときにnameもセレクトしたいってなったらこれはエラーになります。

なぜなら、2021-06-01 aaa,cccとなるからです。
nameカラムに2つのデータはセレクトできません。

もしnameもグループ化させたいなら
groupBy('日付','name')と2つ選択してやれば、

2021-06-01 aaa
2021-06-01 ccc
2021-07-21 ddd

こんな感じにデータが取得できます。