feat: order cinema movies by Hacker News reference count

Movies are now ranked by how many times their IMDb page is referenced
in HN comments (links table, host=www.imdb.com, field=1, matched via param column).
Uses a correlated subquery for COUNT(*) from links.

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')