前提・実現したいこと
とあるリアル店舗で行われるイベントを管理するアプリケーションを開発しています。
質問の機能は過去のイベントを検索して一覧表示+ページネーションをつけるものになります。
必要なテーブルを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
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/09/03 03:53