3つのテーブルを使った対応表をCSV出力する方法がわからない

Question

前提・実現したいこと

• PHP7.2
• Laravel5.6
• MySQL5.6

イベント出欠確認システムを作成しております。
ユーザーごとのイベント毎の出欠を確認するCSV出力する機能の作成途中に行き詰っています。
どうぞよろしくお願いいたします。

usersテーブル

id	name	age
1	一郎	30
2	次郎	29
3	三郎	28
4	四子	27
5	五子	26
6	六子	25

eventsテーブル

id	name	date
1	第1回イベント	2018-01-01
2	第2回イベント	2018-02-01
3	第3回イベント	2018-03-01

event_userテーブル

id	user_id	event_id	attendance
1	1	1	出席
2	3	1	出席
3	5	1	欠席
4	1	2	出席
5	5	2	出席
6	6	2	欠席
7	5	6	出席
8	1	6	出席
9	4	3	出席
10	6	5	欠席

※event_userテーブルのuser_id,event_id はそれぞれテーブルIDと外部キー接続しています。

CSV出力イメージ
|||第1回イベント|第2回イベント|第3回イベント|第4回イベント|第5回イベント|第6回イベント|
|:--:|:--:|:--:|:--:|
|ユーザー名|年齢|出欠状態|出欠状態|出欠状態|出欠状態|出欠状態|出欠状態|
|一郎|30|出席|出席||||出席
|次郎|29||||||
|三郎|28|出席|||||
|四子|27|||出席|||
|五子|26|欠席|出席||||出席
|六子|25||欠席|||欠席|

データ取得の方法が思いつかず、詰まっています。

・データがない座標（例えば次郎はどのイベントにも参加していない）も含めて、NULLで出力したい
・各ユーザー、各イベントの出欠状態が一覧で見れる形であれば現在のイメージ図にはこだわらない

該当のソースコード

app/UserController.php

     /* ユーザーごとの出欠・領収履歴　CSVダウンロード機能*/
    public function csvAttendanceListDownload(Event $event, User $user, EventUser $eventuser)
    {
        $current = Carbon::now();
        $formats = array();
        #ファイル名の指定
        $file_name = 'event_user_' . date('YmdHis') . '.csv';
        $file_path = 'temp/' . $file_name;

        #ファイルの生成
        $file = new \SplFileObject($file_path, 'w');
        if ($file === false) {
            throw new Exception('ファイルの書き込みに失敗しました。');
        }

         #出力する項目
        //ユーザーデータ
        $query_select_users = ['users.*', 'event_user.id as event_user_id', 'event_user.user_id as event_user_user_id', 'event_user.event_id as event_user_event_id', 'event_user.attendance_status'];

        //イベントに対応したユーザーの出欠・領収情報の表示
        $query_select_event_user = ['event_user.*', 'users.id as userId', 'events.id as eventId'];

        #取得データの生成
        //イベント情報
        $events = Event::get();
        $count = count($events);
        //ユーザー情報
        $download_users = User::select($query_select_users)
            ->leftJoin('event_user', function ($join_user) {
                $join_user->on('users.id', '=', 'event_user.user_id');
            })
            ->get();
        //ユーザー毎とイベント毎の出欠状態
        $download_event_users = Event::select($query_select_event_user)
            ->leftJoin('event_user', function ($join_event_user) {
                $join_event_user->on('events.id', '=', 'event_user.event_id');
            })
            ->rightJoin('users', function ($join_event_user) {
                $join_event_user->on('users.id', '=', 'event_user.user_id');
            })
        ->get();
        #各行列の配置
        //1行目は必要なイベント名を出力
        $column_name_1 = array('', '');
        //2行目は各データとイベントのカウント数「出欠状態」項目の表示
        $column_name_2 = array('ユーザー名', '年齢');
        //
        foreach($download_events as $download_event=> $val) {
            $column_name_1[] = $add_event_name;
        }
        mb_convert_variables('SJIS-win', 'UTF-8', $column_name_1);
        $file->fputcsv($column_name_1);
        $i = 0;
        while ($i < $count) {
            $add = '出欠';
            $column_name_2[] = $add;
            $i++;
        }
        mb_convert_variables('SJIS-win', 'UTF-8', $column_name_2);
        $file->fputcsv($column_name_2);

        #データの出力
        foreach ($download_users as $download_user => $user) {
            #文字コード変換と整形
            $formats = array(
                            array(
                                mb_convert_encoding($user->name, 'SJIS-win', 'UTF-8'),//ユーザー名
                                mb_convert_encoding($user->age, 'SJIS-win', 'UTF-8'),//年齢
                            )
                        );
            foreach ($formats as $format => $value) {
                $file->fputcsv($value);
            };
        }

        #出欠状態
        foreach ($download_event_users as $download_event_user => $event_user) {
            $add_attendance_status = mb_convert_encoding($event_user->attendance, 'SJIS-win', 'UTF-8');//出欠状態
            //出欠状態　CSVへ追記
            foreach ($formats as $format => $value) {
                $value[] = $add_attendance_status;
                $file->fputcsv($value);
            }
        }

        #ファイルの情報
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $file_name . '"');
        header('Content-Length: ' . filesize($file_path));
        readfile($file_path);
        return response()->download($file_path);
    }

試したこと

現状は、event_userに登録されているユーザーデータがすべて出力され、座標など関係なく、出欠状態データが出力されています。
何か参考になりそうなサイトなどご紹介いただければ幸いです。

何卒よろしくお願いいたします。

データイメージ

ユーザー名	年齢	（event_id）	(attendance)
一郎	30	1,2,,,,6	出席,出席,,,,出席
次郎	29	,,,,,	,,,,,
三郎	28	1,,,,,	出席,,,,,
四子	27	,,3,,,	,,出席,,,
五子	26	1,2,,,5,	欠席,出席,,,出席
六子	25	,2,,,5,	,欠席,,,欠席,

Answer 1

CSVに関してはとりあえず置いておきます

• 元データ

create table users(uid int primary key,uname varchar(20),age int);
create table events(eid int primary key,ename varchar(20),`date` date);
create table event_user(euid int primary key,uid int not null,eid int not null,attendance set('出席','欠席'),unique key(uid,eid));

insert into users values
(1,'一郎',30),
(2,'次郎',29),
(3,'三郎',28),
(4,'四子',27),
(5,'五子',26),
(6,'六子',25);

insert into events values
(1,'第1回イベント','2018-01-01'),
(2,'第2回イベント','2018-02-01'),
(3,'第3回イベント','2018-03-01'),
(4,'第4回イベント','2018-04-01'),
(5,'第5回イベント','2018-05-01'),
(6,'第6回イベント','2018-06-01');

insert into event_user values
(1,1,1,'出席'),
(2,3,1,'出席'),
(3,5,1,'欠席'),
(4,1,2,'出席'),
(5,5,2,'出席'),
(6,6,2,'欠席'),
(7,5,6,'出席'),
(8,1,6,'出席'),
(9,4,3,'出席'),
(10,6,5,'欠席');

• 集計

select '' as uname,'' as age
,group_concat(case eid when 1 then ename else '' end separator '') as ev1
,group_concat(case eid when 2 then ename else '' end separator '') as ev2
,group_concat(case eid when 3 then ename else '' end separator '') as ev3
,group_concat(case eid when 4 then ename else '' end separator '') as ev4
,group_concat(case eid when 5 then ename else '' end separator '') as ev5
,group_concat(case eid when 6 then ename else '' end separator '') as ev6
from events
group by uname
union all
select 'ユーザー名','年齢','出欠状態','出欠状態','出欠状態','出欠状態','出欠状態','出欠状態'
union all
select uname,age
,group_concat(case eid when 1 then attendance else '' end separator '') as ev1
,group_concat(case eid when 2 then attendance else '' end separator '') as ev2
,group_concat(case eid when 3 then attendance else '' end separator '') as ev3
,group_concat(case eid when 4 then attendance else '' end separator '') as ev4
,group_concat(case eid when 5 then attendance else '' end separator '') as ev5
,group_concat(case eid when 6 then attendance else '' end separator '') as ev6
from users as t1
inner join event_user as t2 using(uid)
group by uid;

Comments

[waiemu]

早速のご回答をありがとうございます。
group_concat　については使用したことのない関数でしたので、今使い方を調べながら進めさせていただいております。
アドバイスいただいたことを自分なりに解釈してみたのですが、追加した「データイメージ」で間違いありませんでしょうか？
もし、誤っているなど、ご指摘ありましたら、またコメントいただければ幸いです。

[yambejp]

> データイメージ

いや、むしろ私がつけたサンプルが命題にある「CSV出力イメージ」と同等なのですが
そのまま実行して確認してもらえませんか？

[waiemu]

SQL文を実行したところ、イメージ通りのデータが取得できました。
ご指摘ありがとうございます。

ただ、2つ目のクエリ文ですが、次郎（event_userにuidが無いユーザー）も含めてデータ取得することと、（これはinner joinではなく、left joinで解決？）

eid の数と番号は、変動するので、データ取得するイベントIDを参照しながら、イベント数だけ「出欠状況」のカラムを追加するコードが必要でした。

この部分がLaravelのクエリビルダを使いながら実行できるか試してみたいと思います。
ありがとうございます。

[yambejp]

イベントの数だけ全通り自動的に処理するならSQL側でやるなら
procedureで解決してください
php側でやっても構わないなら先行してイベント一覧を取得して
sql文をプログラムで構築します

Answer 2

yambejp様　ありがとうございました。
遅くなりましたが、ようやく完成しました。
思った通りのデータができました。

app/UserController.php

public function csvAttendanceListDownload(Event $event, User $user, EventUser $eventuser)
    {
        /*ファイル名の指定 パスの指定*/
        $file_name = 'event_user_' . date('YmdHis') . '.csv';
        $file_path = 'temp/' . $file_name;

        /*ファイルの生成
        $file = new \SplFileObject($file_path, 'w');
        if ($file === false) {
            throw new Exception('ファイルの書き込みに失敗しました。');
        }

        //イベントデータ
        $download_events = Event::get();
        $event_count = count($download_events);//イベントの数
        
        //1行目はカウントが必要なイベント名を出力
        $column_name_1 = ['', ''];//名前と年齢の列を空けておく
        foreach ($download_events as $download_event) {
            $column_name_1[] = $download_event->name;
        }
        
        mb_convert_variables('SJIS-win', 'UTF-8', $column_name_1);
        $file->fputcsv($column_name_1);
        
        //2行目は各データと出力するイベント分の数だけ「出欠状態」項目の表示
        $i = 0;
        $column_name_2 = array('名前', '年齢');
        while ($i < $event_count) {
            $add_attend_column = '出欠';
            $column_name_2[] = $add_attend_column;
            $i++;
        }
        mb_convert_variables('SJIS-win', 'UTF-8', $column_name_2);
        $file->fputcsv($column_name_2);
        $eventusers = EventUser::get();
        $select_user_data = ['users.name',
                             'users.age',
                            ];
        $bindings = [];
/*テーブル出力準備*/
foreach ($download_events as $download_event) {
            $select_user_data[] = (DB::raw(
                "MAX(CASE WHEN event_user.event_id = ? THEN event_user.attendance_status ELSE NULL END) AS attendance_" . $int
            ));
            $bindings[] = $download_event->id;
            $int++;
        }
        
         $download_users = User::select($select_user_data)
                ->addBinding($bindings, 'select')//addBindingで「？」の指定
                ->leftJoin('event_user', 'users.id', '=', 'event_user.user_id')
                ->groupBy('users.id')//database,php --strictをfalseにしなければならな
                ->get()
                ;
        /*文字コード変換と整形*/
        $formats = [];
        foreach ($download_users as $download_user) {
                $formats_row = array(
                    mb_convert_encoding($download_user->name, 'SJIS-win', 'UTF-8'),//名前
                    mb_convert_encoding($download_user->age, 'SJIS-win', 'UTF-8'),//年齢
                //イベントの数だけ出欠状態を追加
                for($i = 0; $i < $event_count; $i++) {
                    $formats_row[] = mb_convert_encoding $download_user->{'attendance_'.$i}), 'SJIS-win', 'UTF-8');//出欠状態
                }
                $formats[] = $formats_row;
        }
         foreach ($formats as $val) {
            $file->fputcsv($val);
        }
        //ファイルの情報
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $file_name . '"');
        header('Content-Length: ' . filesize($file_path));
        readfile($file_path);
        return response()->download($file_path);
    }