取得した縦のデータを横に表示したいです

Question

前提・実現したいこと

予約データから必要なデータ一週間分を横に出したいです。
【例】

本　予約開始日　予約開始時間　予約終了予定日　予約終了予定時間

本1　6/8　　　12:00　　　　　6/8　　　　　　18:00
本1　6/8 18:00 6/9 12:00
本1 6/9 13:00 6/10 18:00
本2 6/8 10:00 6/9 18:00
本2 6/10 10:00 6/10 18:00
本3
…

上記のデータを以下のように横に表示したいです。

本　　6/8 6/9 6/10 …　6/14
本1 12:00-18:00 18:00-24:00 00:00-12:00 13:00-18:00
本2 10:00-24:00 00:00-18:00 10:00-18:00
本3
…

DBはpostgreSQLを使っています。
SQLで一発で取得できるのでしょうか？

私は取得できなかったため、PHPで処理しようとしていますが
うまくできません。

おわかりの方、ご教示お願いします。
できればSQLを教えて頂けたら幸いです。
もしくはPHPでどう配列にもたせるか教えて頂けたら幸いです。

よろしくお願いします。

【追記】
画面表示としては図のようにHTML側で表現します。

日付ごとにセルがあり、このセルは固定値となっているのと、本の予約率算出の都合で
日をまたぐ予約をわざと24:00で分割しています。

この形式になるようにデータを取得したいです。
実際に書いたソースは以下の通りですが、取得した配列をhtml側でどう表現すればいいか悩んでいます。
配列自体はできているのですが、配列の二次元目が予約番号？となってしまい、html側で日付ごとに
配列を区切れずにいます。

// 実現したい配列
$res_array["本のコード"]["日付"]["予約時間"]
// 現在の配列
$res_array["本のコード"]["予約の行数"]["予約時間"]

// 重複している本をひとまとめにする
// SQLで一番上に書いたデータを取得　→　$予約リスト

$book_list = array();
$tmp = "";
foreach($予約リスト as $res){
 if($tmp != $res["本のコード"]){
  $tmp = $res["本のコード"];
  $book_list = $res["本のコード"];
 }
}
$res_array = array();
$i = 0;
$j = 0;
// 表現したい配列の生成　←ここがだめ
foreach($book_list as $book){
 foreach($予約リスト as $res){
  if($book["本のコード"] == $res["本のコード"]){
   $res_array[$i][$j] = $res["本のコード"];
   $book_list = $res["本のコード"];
   if(!in_null($res["予約時間"])){
    $res_array[$i][$j]["予約時間の長さ"] = $res["予約時間の長さ"];
   }
  }
  $j++;
 }
 $i++;
}

Comments

[m.ts10806]

その形で出力したいのか取得だけなのか　どちらでしょうか。また、PHPで実際にやってみたコードをご提示ください。「うまくできません」ではどうやって何が起きたのか見ている人には分かりません。想定通りできていないもので結構なので提示してください。ちょっと間違っているだけかもしれません。ただしそれは実際のコードを見ないと判断できません。 https://teratail.com/help/question-tips#questionTips3-5

[sazi]

00:00とか24:00とかのルールが良く分かりませんね。

Answer 1

SQL側で日付を区切るようにしてみました。
処理の為に、sql内でtimestamp型にcastしてます。その際年度を直接付加しているので、
その辺は考慮して下さい。
また、24:00という表現はできないので、23:59としています。

#データ

CREATE TABLE "予約データ"
(
  "本" text,
  "予約開始日" text,
  "予約開始時間" text,
  "予約終了予定日" text,
  "予約終了予定時間" text
);
insert into "予約データ" values 
('本1','06/08','12:00','06/08','18:00')
,('本1','06/08','18:00','06/09','12:00')
,('本1','06/09','13:00','06/10','18:00')
,('本2','06/08','10:00','06/09','18:00')
,('本2','06/10','10:00','06/10','18:00')
;

#SQL

・generate_series()で基準となる日付および一日の時間帯(range型)を生成
・開始/終了のrange型で上記と結合
※range型は範囲の上限下限の境界値を「含む/含めない」の指定ができかつ比較用の演算子もありますので、スケジュールなどのデータを扱うには非常に便利です。

with
  カレンダー as (
    select 日付::timestamp 
         , tsrange(日付::timestamp,(日付+'1 days')::timestamp,'[)') as 日付範囲 
    from generate_series('2018/6/8'::date,'2018/6/10'::date,'1 days') as w(日付)
  )
, 予約状況 as(
  select * 
      , ('2018/' || 予約開始日 || ' ' || 予約開始時間)::timestamp 予約開始日時
      , ('2018/' || 予約終了予定日 || ' ' || 予約終了予定時間)::timestamp as 予定終了日時
  from 予約データ
  )
select 本,to_char(日付,'mm/dd') as 日付
     , to_char(case when 日付> 予約開始日時 then 日付 else 予約開始日時 end,'hh24:mi') as 開始時刻
     , to_char(case when 日付+'1 days'<予定終了日時 then 日付+'1 days'+'-1 second' else 予定終了日時 end,'hh24:mi') as 終了時刻
from  カレンダー clnd left join 予約状況 list
      on clnd.日付範囲 && tsrange(list.予約開始日時,list.予定終了日時,'[)')
order by 本, 日付, 予約開始日時

#結果

本	日付	開始時刻	終了時刻
本1	06/08	12:00	18:00
本1	06/08	18:00	23:59
本1	06/09	00:00	12:00
本1	06/09	13:00	23:59
本1	06/10	00:00	18:00
本2	06/08	10:00	23:59
本2	06/09	00:00	18:00
本2	06/10	10:00	18:00

#本コードごとに纏める
本コード単位で日付、開始時刻、終了時刻を配列化しています。
日付配列のループで添字を使って参照できるように、それぞれの配列は添え字が一致するようにしています。

with
  カレンダー as (
    select 日付::timestamp 
         , tsrange(日付::timestamp,(日付+'1 days')::timestamp,'[)') as 日付範囲 
    from generate_series('2018/6/8'::date,'2018/6/10'::date,'1 days') as w(日付)
  )
, 予約状況 as(
    select * 
        , ('2018/' || 予約開始日 || ' ' || 予約開始時間)::timestamp 予約開始日時
        , ('2018/' || 予約終了予定日 || ' ' || 予約終了予定時間)::timestamp as 予定終了日時
    from 予約データ
  )
, 日別状況 as (
    select 本,to_char(日付,'mm/dd') as 日付
         , to_char(case when 日付> 予約開始日時 then 日付 else 予約開始日時 end,'hh24:mi') as 開始時刻
         , to_char(case when 日付+'1 days'<予定終了日時 then 日付+'1 days'+'-1 second' else 予定終了日時 end,'hh24:mi') as 終了時刻
    from  カレンダー clnd left join 予約状況 list
          on clnd.日付範囲 && tsrange(list.予約開始日時,list.予定終了日時,'[)')
  )
select 本
     , array_agg(日付 order by 日付) as 日付リスト
     , array_agg(開始時刻 order by 日付, 開始時刻) as 開始時刻リスト
     , array_agg(終了時刻 order by 日付, 開始時刻, 終了時刻) as 終了時刻リスト
from  日別状況
group by 本
order by 本

#結果

本	日付リスト	開始時刻リスト	終了時刻リスト
本1	{06/08,06/08,06/09,06/09,06/10}	{12:00,18:00,00:00,13:00,00:00}	{18:00,23:59,12:00,23:59,18:00}
本2	{06/08,06/09,06/10}	{10:00,00:00,10:00}	{23:59,18:00,18:00}

Comments

[tanakasan]

わざわざソースまでありがとうございます！
なんとか上の結果までたどりつくことができました。
問題は縦に表示されている検索結果を、上で載せた図のように

本１　予約日１・予約時間（複数件あっても可）　予約日２・予約時間　予約日３・予約時間
本２　予約日１・予約時間　予約日２・予約時間　予約日３・予約時間
本３　予約日１・予約時間　予約日２・予約時間　予約日３・予約時間
…

というような結果を生成したいのですが、自分のスキルがないせいで、できずに困っています…。

[sazi]

追記しました。
ただ、表示しようとしているのは日付を基準で行った方が処理しやすくないですか？
要は、表の列を先に決めてから、行の処理を行うのがスムーズじゃないかという事

[sazi]

SQLも日付、本コード、開始（配列）、終了（配列）となっていた方が、
日付と本コードがクロスする場所に配列を展開すれば良いだけなので。

[tanakasan]

仰る通り、日付基準で処理しようとしていたかもしれません。
htmlでは
　 　　<th>6/8</th>　　　　<th>6/9</th>　　<th>6/10</th>…
<td>本1＜/td＞<td>予約1,予約2,…＜/td＞<td>予約1…＜/td＞<td>予約1…＜/td＞…
<td>本2＜/td＞…
<td>本3＜/td＞…

というようにテーブルを表示しています。
一つ質問ですが、
array_aggの配列展開はforeach文などで行うのでしょうか？
今、開発環境がないので試せないのと初心者で色々聞いて申し訳ないです…。

[sazi]

SQLでarray_to_string(array_agg(),',')のようにカンマ区切りの文字列にして、PHPではsplitで配列分解とかでもできると思います。
PHPは詳しくないので、別質問の方が良いかと思います。

[tanakasan]

なるほど！
ありがとうございます！
試してみます！

Answer 2

array_agg が使えるかと。

Comments

[tanakasan]

ちょっと試してみます
ありがとうございます！