group_concatで抽出したエイリアスに対してwhereで絞り込みをかけたい

Question

前提・実現したいこと

とあるリアル店舗で行われるイベントを管理するアプリケーションを開発しています。

質問の機能は過去のイベントを検索して一覧表示＋ページネーションをつけるものになります。
必要なテーブルをJOINして、group_byでイベントのID毎にまとめていますが、その中でイベント開催日程はイベントに対して複数あり、その検索方法で詰まってしまっています。

詰まっている点としては、SUBSTRING_INDEXとgroup_concatで抽出したエイリアス（selectの中で付けた別名）に対してwhereで絞り込みをかけたいです。

具体的にはイベントの実施期間の最小値と最大値を割り出して、指定した開催時期と被るかどうかで、whereの絞り込みをかけたく実装しましたが、select節での別名はwhereに使用できないので、困っています。

発生している問題・エラーメッセージ

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'start_times' in 'where clause'

SQL: select count(*) as aggregate from `ep_events` left join `event_places` as `ep` on `ep_events`.`event_place_id` = `ep`.`id` left join `event_dates` as `ed` on `ep_events`.`id` = `ed`.`event_id` left join `buildings` as `b` on `ep`.`building_id` = `b`.`id` left join `building_categories` as `bc` on `b`.`category_id` = `bc`.`id` where `ep_events`.`status` = 1 and date(`ep_events`.`created_at`) > 2016-09-03 and `start_times` <= 2019-07-01 group by `ep_events`.`id`)

該当のソースコード

イベント -< イベント日程（1：多）なので、DB::raw()の部分で並べ替えつつ、最初の1つだけ抽出することにより、イベントの最古開催日を最新終了日は別名で取れています。

$query = EpEvent::query()->select('b.*', 'ep.*', 'ep_events.*', 'ed.start_time', 'ed.end_time',
          'ep.name AS place_name', 'b.name AS building_name', 'bc.name AS category_name',
          DB::raw("SUBSTRING_INDEX(group_concat(ed.start_time ORDER BY ed.start_time ASC),',',1) AS start_times"),
          DB::raw("SUBSTRING_INDEX(group_concat(ed.end_time ORDER BY ed.end_time DESC),',',1) AS end_times"))
            ->leftJoin('event_places AS ep', 'ep_events.event_place_id', '=', 'ep.id')
            ->leftJoin('event_dates AS ed', 'ep_events.id', '=', 'ed.event_id')
            ->leftJoin('buildings AS b', 'ep.building_id', '=', 'b.id')
            ->leftJoin('building_categories AS bc', 'b.category_id', '=', 'bc.id')
            ->where('ep_events.status', 1)
            ->whereDate('ep_events.created_at', '>', Carbon::now()->subYear(3)->toDateString())
            ->orderBy('ep_events.created_at', 'DESC');

$query->groupBy('ep_events.id')->paginate(5);

検索部分

 if (!empty($searchEventStartDate)) {
    $query->where('start_times', '<=', $searchEventStartDate);
 }
 if (!empty($searchEventEndDate)) {
    $query->where('end_times', '<=', $searchEventEndDate);
 }

テーブル構造

ep_events

CREATE TABLE `ep_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `place` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `booking_begin_time` time DEFAULT NULL,
  `booking_begin_day` date DEFAULT NULL,
  `booking_end_time` time DEFAULT NULL,
  `booking_end_day` date DEFAULT NULL,
  `booking_interval` int(11) DEFAULT 3600,
  `begin_time` time DEFAULT '10:00:00',
  `begin_day` date DEFAULT NULL,
  `client_id` int(11) NOT NULL DEFAULT 1,
  `user_id` int(11) NOT NULL DEFAULT 1,
  `password` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_day` date DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `booking_time_note` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `period` int(11) DEFAULT NULL,
  `staff_count` int(11) DEFAULT NULL,
  `goal` int(11) DEFAULT NULL,
  `goal_unit` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `goal_detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `result` tinyint(4) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `number_detail` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_place_id` int(11) DEFAULT NULL,
  `event_category_id` int(11) DEFAULT NULL,
  `attraction` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image5` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 1,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

event_dates

CREATE TABLE `event_dates` (
  `event_id` int(10) unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

event_places

CREATE TABLE `event_places` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `building_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `floor` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `location` tinyint(4) NOT NULL,
  `layout` tinyint(4) NOT NULL,
  `size` int(11) NOT NULL,
  `memo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image2` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image3` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image4` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `image5` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

buildings

CREATE TABLE `buildings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `zipcode` int(10) unsigned DEFAULT NULL,
  `pref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `street` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gmap_url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `building_logo_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

試したこと

groupBy()しているので、Havingを試しました。

 if (!empty($searchEventStartDate)) {
    $query->having('start_times', '<=', $searchEventStartDate);
 }
 if (!empty($searchEventEndDate)) {
    $query->having('end_times', '<=', $searchEventEndDate);
 }

サブクエリでwhere内で最古と最新の開催日程を抽出しましたが、これもうまくいきませんでした。

      if (!empty($searchEventStartDate)) {
            $query->where('start_times', '>=', function ($q) {
                $q->from('ep_events')->select(DB::raw("SUBSTRING_INDEX(group_concat(ed.start_time ORDER BY ed.start_time ASC),',',1) AS start_times"))
                  ->leftJoin('event_dates AS ed', 'ep_events.id', '=', 'ed.event_id')
                  ->where('ep_events.status', '1')
                  ->where('ep_events.id', 1)
                  ->groupBy('ep_events.id');
            });
        }

他にこの要件を実現する方法が思い浮かばないため、何か気づいたことがあればご助言いただけると幸いです。

補足情報（FW/ツールのバージョンなど）

• PHP: 7.3
• MariaDB: 10.3.8
• Laravel: 5.8

Answer 1

流れ的にはgroup_concatをしたselect文をサブクエリにするのかなとも
思いますが、正直group_concatで作成された文字列にあれこれするくらいなら
グループ化する前のデータを利用して検索をするほうが
パフォーマンス的にも有利ですし、単純なSQLで処理できると思います

Comments

[uchida_yuma]

ご回答ありがとうございました！
そもそも全体を見直して、ご提示の通りグループ化する前のデータで実現しました。