laravel9子查詢 · Laravel

方式0：JoinSub形式 ``` SELECT `ve`.`id`, `ve`.`sort_id`, `ve`.`video_id` FROM `video_episodes` AS `ve` INNER JOIN ( SELECT `video_id`, MAX( sort_id ) AS sort_id FROM `video_episodes` WHERE `video_type` = 10 GROUP BY `video_id` ) AS `t` ON `t`.` video_id` = `ve`.`video_id` AND `t`.`sort_id` = `ve`.`sort_id` WHERE `ve`.`video_type` = 10 ORDER BY `ve`.`video_id` DESC ``` 方式1：leftJoinSub(JoinSub形式) ``` /** * 內容消費分日部分 * * @param array $search * @return Builder */ public static function commonVideoContentDaily(array $search): Builder { // 根據國家確定使用的表 if (!empty($search['country_code'])) { $spendQuery = DorisChannelLinkCountryTrend::query(); } else { $spendQuery = DorisChannelLinkTrend::query(); } // 查詢投放成本表 $subQuery = $spendQuery ->selectRaw('event_date,video_id,SUM(spend)/100 as spend_sum') ->when(!empty($search['start_date']) && !empty($search['end_date']), function ($q) use ($search) { $q->whereBetween('event_date', [$search['start_date'], $search['end_date']]); }) ->when(!empty($search['app_id']), function ($q) use ($search) { if (!is_array($search['app_id'])) { $search['app_id'] = explode(',', $search['app_id']); } $q->whereIn('app_id', $search['app_id']); }) ->when(!empty($search['system']), function ($q) use ($search) { $q->where('system', $search['system']); }) ->when(!empty($search['video_ids']), function ($q) use ($search) { if (!is_array($search['video_ids'])) { $search['video_ids'] = explode(',', $search['video_ids']); } $q->whereIn('video_id', $search['video_ids']); }) ->when(!empty($search['country_code']), function ($q) use ($search) { if (!is_array($search['country_code'])) { $search['country_code'] = explode(',', $search['country_code']); } $q->whereIn('country_code', $search['country_code']); }) ->groupBy(['event_date', 'video_id']); // 內容消費分日表和投放成本查詢到的query作為臨時表進行left關聯、短劇表進行關聯，查詢字段 $column = VideoExpenseDaily::handleStatColumn(); $fields = implode(',', array_merge($column, ["spend_sum"])); return DorisVideoExpenseDaily::search($search) ->from(DorisVideoExpenseDaily::getIns()->getTable() . " as daily") ->selectRaw($fields) ->join(DorisVideo::getIns()->getTable() . ' as v', 'daily.video_id', "=", 'v.id') ->leftJoinSub($subQuery, 't', function ($join) { $join->on('daily.video_id', '=', 't.video_id')->on('daily.date', '=', 't.event_date'); }) ->with(['video' => function ($query) { $query->select('id', 'name', 'third_video_id', 'platform', 'video_num', 'language', 'shelve_time') ->with(['videoTypeRelation:id,video_id,type_id', 'videoTypeRelation.videoType:id,name']); }]) ->groupBy(['date', 'daily.video_id', 'spend_sum']) ->orderByDesc('date'); } ``` 方式2：toSql+參數綁定形式 ``` public static function commonVideoContentDaily(array $search): Builder { // 根據國家確定使用的表 if (!empty($search['country_code'])) { $spendQuery = DorisChannelLinkCountryTrend::query(); } else { $spendQuery = DorisChannelLinkTrend::query(); } // 查詢投放成本表 $tempTableSubQuery = $spendQuery ->selectRaw('event_date,video_id,SUM(spend)/100 as spend_sum') ->when(!empty($search['start_date']) && !empty($search['end_date']), function ($q) use ($search) { $q->whereBetween('event_date', [$search['start_date'], $search['end_date']]); }) ->when(!empty($search['app_id']), function ($q) use ($search) { if (!is_array($search['app_id'])) { $search['app_id'] = explode(',', $search['app_id']); } $q->whereIn('app_id', $search['app_id']); }) ->when(!empty($search['system']), function ($q) use ($search) { $q->where('system', $search['system']); }) ->when(!empty($search['video_ids']), function ($q) use ($search) { if (!is_array($search['video_ids'])) { $search['video_ids'] = explode(',', $search['video_ids']); } $q->whereIn('video_id', $search['video_ids']); }) ->when(!empty($search['country_code']), function ($q) use ($search) { if (!is_array($search['country_code'])) { $search['country_code'] = explode(',', $search['country_code']); } $q->whereIn('country_code', $search['country_code']); }) ->groupBy(['event_date', 'video_id']); // 內容消費分日表和投放成本查詢到的query作為臨時表進行left關聯、短劇表進行關聯，查詢字段 $column = VideoExpenseDaily::handleStatColumn(); $fields = implode(',', array_merge($column, ["spend_sum"])); return DorisVideoExpenseDaily::query() ->from(DorisVideoExpenseDaily::getIns()->getTable() . " as daily") ->selectRaw($fields) ->join(DorisVideo::getIns()->getTable() . ' as v', 'daily.video_id', "=", 'v.id') ->leftJoin(DB::raw("(" . $tempTableSubQuery->toSql() . ") as t"), function ($join) use ($tempTableSubQuery) { $join->on('daily.video_id', '=', 't.video_id')->on('daily.date', '=', 't.event_date'); // 綁定子查詢的參數 foreach ($tempTableSubQuery->getBindings() as $binding) { $join->addBinding($binding, 'join'); } }) ->with(['video' => function ($query) { $query->select('id', 'name', 'third_video_id', 'platform', 'video_num', 'language', 'shelve_time') ->with(['videoTypeRelation:id,video_id,type_id', 'videoTypeRelation.videoType:id,name']); }]) ->when(!empty($search['start_date']) && !empty($search['end_date']), function ($q) use ($search) { $q->whereBetween('date', [$search['start_date'], $search['end_date']]); }) ->when(!empty($search['app_id']), function ($q) use ($search) { if (!is_array($search['app_id'])) { $search['app_id'] = explode(',', $search['app_id']); } $q->whereIn('daily.app_id', $search['app_id']); }) ->when(!empty($search['system']), function ($q) use ($search) { $q->where('daily.system', $search['system']); }) ->when(!empty($search['video_ids']), function ($q) use ($search) { if (!is_array($search['video_ids'])) { $search['video_ids'] = explode(',', $search['video_ids']); } $q->whereIn('daily.video_id', $search['video_ids']); }) ->when(!empty($search['country_code']), function ($q) use ($search) { if (!is_array($search['country_code'])) { $search['country_code'] = explode(',', $search['country_code']); } $q->whereIn('daily.country_code', $search['country_code']); }) ->when(!empty($search['platform']), function ($q) use ($search) { if (!is_array($search['platform'])) { $search['platform'] = explode(',', $search['platform']); } $q->whereIn('v.platform', $search['platform']); }) ->groupBy(['date', 'daily.video_id', 'spend_sum']) ->orderByDesc('date'); } ```