方式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');
}
```
- Laravel下载
- 项目管理
- Manager
- Vip专属链接管理
- Api
- Vip专属链接管理(Api)
- php artisan route:list 路由显示不全
- 数据迁移和填充
- Laravel5.5事件监听机制（注册-监听-任务调度）
- 章节1：未启用任务调度
- 章节2：启用任务调度
- 使用记录
- 数据迁移使用记录
- 安装laravel5.5日志观察器
- Laravel5.5消息队列（rabbitmq）
- 1：laravel自带消息队列
- 2：RabbitMq队列使用
- 第三方支付扩展：yansongda/laravel-pay
- 安装指引
- 控制器内使用
- 分表查询（mysql+mongo）
- 前端Vue按钮导出问题
- 单元测试
- 模型使用
- laravel9数据填充
- laravel9子查询
- Laravel反射机制