public function episodes()
{
$episodes = Episode::join('dramas', function ($join) {
$join->on('episodes.drama_id', '=', 'dramas.id');
})->select('dramas.title as dramaTitle', 'dramas.type as type', 'dramas.original as original', 'episodes.id as episodeId', 'episodes.title as episodeTitle', 'episodes.release_date as releaseDate', 'dramas.sc as sc', 'episodes.alias as alias', 'episodes.poster_url as posterUrl', 'dramas.era as era', 'dramas.genre as genre', 'dramas.state as state', 'episodes.duration as duration')->orderByRaw('release_date desc, episodes.id desc')->simplePaginate(20);
return $episodes;
}
public function index(Request $request)
{
//判断url中是否有性向参数，若没有就默认性向为耽美type=0
if ($request->has('type')) {
$type = $request->input('type');
} else {
$type = 0;
}
//若type为-1，则查询全部新剧，否则按性向即type值查询
if ($type < 0) {
$episodes = Episode::join('dramas', function ($join) {
$join->on('episodes.drama_id', '=', 'dramas.id')->where('episodes.release_date', '>=', date("Y-m-d", strtotime("-7 day")));
})->select('dramas.title as drama_title', 'dramas.type as type', 'dramas.original as original', 'episodes.id as episode_id', 'episodes.title as episode_title', 'episodes.reviews as reviews', 'episodes.release_date as release_date', 'dramas.sc as sc', 'episodes.alias as alias', 'episodes.poster_url as poster_url', 'dramas.era as era', 'dramas.genre as genre', 'dramas.state as state', 'episodes.duration as duration')->orderBy('episode_id', 'desc')->get();
} else {
$episodes = Episode::join('dramas', function ($join) use($type) {
$join->on('episodes.drama_id', '=', 'dramas.id')->where('dramas.type', '=', $type)->where('episodes.release_date', '>=', date("Y-m-d", strtotime("-7 day")));
})->select('dramas.title as drama_title', 'dramas.type as type', 'dramas.original as original', 'episodes.id as episode_id', 'episodes.title as episode_title', 'episodes.reviews as reviews', 'episodes.release_date as release_date', 'dramas.sc as sc', 'episodes.alias as alias', 'episodes.poster_url as poster_url', 'dramas.era as era', 'dramas.genre as genre', 'dramas.state as state', 'episodes.duration as duration')->orderBy('episode_id', 'desc')->get();
}
$top10 = $episodes->take(10);
//将一周新剧按发剧日期分组
$episodes = $episodes->groupBy('release_date');
//查询60天收藏数前10的剧单
$lists = Listfav::with(['dramalist' => function ($query) {
$query->select('id', 'title');
}])->select(DB::raw('count(*) as favorite_count, list_id'))->where('created_at', '>=', date("Y-m-d H:i:s", strtotime("-60 day")))->groupBy('list_id')->orderBy('favorite_count', 'desc')->take(10)->get();
//最新剧单
$newlists = Dramalist::select('id', 'title')->orderBy('id', 'desc')->take(10)->get();
//查询30天评论数前10的剧集id
$hotDramas = Review::with(['drama' => function ($query) {
$query->select('id', 'title');
}])->where('created_at', '>=', date("Y-m-d H:i:s", strtotime("-30 day")))->select(DB::raw('count(*) as review_count, drama_id'))->groupBy('drama_id')->orderBy('review_count', 'desc')->take(10)->get();
//查询30天收藏前10的剧集id
$hotFavorites = Favorite::with(['drama' => function ($query) {
$query->select('id', 'title');
}])->where('created_at', '>=', date("Y-m-d H:i:s", strtotime("-30 day")))->select(DB::raw('count(*) as favorite_count, drama_id'))->groupBy('drama_id')->orderBy('favorite_count', 'desc')->take(10)->get();
//查询最新10条新剧的添加历史
$versions = Dramaver::select('user_id', 'drama_id', 'created_at')->where('first', 1)->orderBy('drama_id', 'desc')->take(10)->get();
$versions->load(['user' => function ($query) {
$query->select('id', 'name');
}]);
$versions->load(['drama' => function ($query) {
$query->select('id', 'title', 'sc');
}]);
$versions = $versions->filter(function ($version) {
return isset($version->drama->id);
});
return view('index', ['type' => $type, 'episodes' => $episodes, 'top10' => $top10, 'lists' => $lists, 'newlists' => $newlists, 'hotDramas' => $hotDramas, 'hotFavorites' => $hotFavorites, 'versions' => $versions]);
}
/**
* Display a listing of the resource.
*
* @return Response
*/
public function index(Request $request)
{
//数据库查询参数
$scope = [];
//性向筛选
if ($request->has('type')) {
$scope['dramas.type'] = ['=', $request->input('type')];
}
//时代筛选
if ($request->has('era')) {
$scope['dramas.era'] = ['=', $request->input('era')];
}
//原创性筛选
if ($request->has('original')) {
$scope['dramas.original'] = ['=', $request->input('original')];
}
//进度筛选，结合state与count字段
if ($request->has('state')) {
switch ($request->input('state')) {
case 0:
//连载中
$scope['dramas.state'] = ['=', 0];
break;
case 1:
//已完结
$scope['dramas.state'] = ['=', 1];
$scope['dramas.count'] = ['>', 1];
break;
case 2:
//全一期
$scope['dramas.state'] = ['=', 1];
$scope['dramas.count'] = ['=', 1];
break;
case 3:
//已坑
$scope['dramas.state'] = ['=', 2];
break;
}
}
//起止日期筛选
if ($request->has('startdate') || $request->has('enddate')) {
if (!$request->has('startdate')) {
//如果只有截止日期
$scope['release_date'] = ['<=', $request->input('enddate')];
} else {
if (!$request->has('enddate')) {
//如果只有起始日期
$scope['release_date'] = ['>=', $request->input('startdate')];
} else {
//既有起始日期又有终止日期时需要使用between另外编写查询语句
$release_date = [$request->input('startdate'), $request->input('enddate')];
}
}
}
//主役筛选
if ($request->has('cv')) {
$scope['dramas.sc'] = ['LIKE', '%' . $request->input('cv') . '%'];
}
//SC筛选
if ($request->has('sc')) {
$scope['episodes.sc'] = ['LIKE', '%' . $request->input('sc') . '%'];
}
//传递给视图的url参数
$params = $request->except('page');
//排序
if ($request->has('sort')) {
$params['sort'] = $request->input('sort');
} else {
$params['sort'] = 'id';
}
if ($request->has('order')) {
$params['order'] = $request->input('order');
} else {
$params['order'] = 'desc';
}
//连接剧集表查询
if (isset($release_date)) {
$episodes = Episode::whereBetween('release_date', $release_date)->join('dramas', function ($join) use($scope) {
$join->on('episodes.drama_id', '=', 'dramas.id');
foreach ($scope as $key => $value) {
$join = $join->where($key, $value[0], $value[1]);
}
})->select('episodes.*', 'dramas.id as drama_id', 'dramas.title as drama_title', 'dramas.type as type', 'dramas.era as era', 'dramas.genre as genre', 'dramas.original as original', 'dramas.state as state', 'dramas.sc as cv')->orderBy('episodes.' . $params['sort'], $params['order'])->paginate(20);
} else {
$episodes = Episode::join('dramas', function ($join) use($scope) {
$join->on('episodes.drama_id', '=', 'dramas.id');
foreach ($scope as $key => $value) {
$join = $join->where($key, $value[0], $value[1]);
}
})->select('episodes.*', 'dramas.id as drama_id', 'dramas.title as drama_title', 'dramas.type as type', 'dramas.era as era', 'dramas.genre as genre', 'dramas.original as original', 'dramas.state as state', 'dramas.sc as cv')->orderBy('episodes.' . $params['sort'], $params['order'])->paginate(20);
}
return view('episode.index', ['params' => $params, 'episodes' => $episodes]);
}
