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]); }