diff options
Diffstat (limited to 'app/Http/Controllers')
| -rw-r--r-- | app/Http/Controllers/CinemaController.php | 16 |
1 files changed, 13 insertions, 3 deletions
diff --git a/app/Http/Controllers/CinemaController.php b/app/Http/Controllers/CinemaController.php index a526830..160ca33 100644 --- a/app/Http/Controllers/CinemaController.php +++ b/app/Http/Controllers/CinemaController.php @@ -9,6 +9,13 @@ class CinemaController extends Controller { public function cinema() { + $refCounts = DB::connection('cinema') + ->table('links') + ->select('links.param', DB::raw('COUNT(*) as ref_count')) + ->where('links.host', 'www.imdb.com') + ->where('links.field', 1) + ->groupBy('links.param'); + $movies = DB::connection('cinema') ->table('imdb') ->select( @@ -23,13 +30,16 @@ class CinemaController extends Controller 'imdb.poster_url', 'imdb.title_type', 'imdb.runtime_minutes', - 'imdb.has_people' + 'imdb.has_people', + DB::raw('COALESCE(ref.ref_count, 0) as ref_count') ) - ->orderBy('imdb.average_rating', 'desc') - ->orderBy('imdb.num_votes', 'desc') + ->leftJoinSub($refCounts, 'ref', function ($join) { + $join->on('ref.param', '=', 'imdb.imdb_id'); + }) ->whereNotNull('imdb.primary_title') ->whereNotNull('imdb.wiki_article') ->where('imdb.title_type', 'movie') + ->orderBy('ref_count', 'desc') ->simplePaginate(12); $total = DB::connection('cinema') |