質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.05%

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

解決済

回答 1

投稿

  • 評価
  • クリップ 0
  • VIEW 626

score 44

前提・実現したいこと

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

質問の機能は過去のイベントを検索して一覧表示+ページネーションをつけるものになります。
必要なテーブルを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);
 }

start_timesとend_timesが抽出した別名

テーブル構造

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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+1

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/09/03 12:53

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.05%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る