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

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

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

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

https://www.kamome-susume.com/enginner-job-change-site/

## ゴール

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

postsテーブル

| post_id | amount | created_at |
|:-:|:-:|:-:|
|  1 |  100 |  2021-06-01 12:23:07 |
|  2 |   200|  2021-06-01 15:23:23 |
|  3 |   300|  2021-06-01 16:21:12 |
|  4 |   400|  2021-07-21 19:23:32 |

↓

| created_at | amount | 
|:-:|:-:|
| 2021/06/01  |  600 | 
|  2021/07/21 |   400| 

考え方としては、
・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日と表示されます。

実際の出力画面

![スクリーンショット 2021-07-10 1.19.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1693248/8ad38899-c922-9021-592f-d4d941ad5e9a.png)

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

| post_id | name | created_at |
|:-:|:-:|:-:|
|  1 |  aaa |  2021-06-01 12:23:07 |
|  2 |   aaa|  2021-06-01 15:23:23 |
|  3 |   ccc|  2021-06-01 16:21:12 |
|  4 |   ddd|  2021-07-21 19:23:32 |

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

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

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

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

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

https://www.kamome-susume.com/enginner-job-change-site/