vuchkov · February 8, 2019 13:24 · Aug 3, 2016 · Aug 3, 2016
diff --git a/CommunityLinksQuery.php b/CommunityLinksQuery.php
@@ -12,7 +12,7 @@ class CommunityLinksQuery
      * @param  string|null  $channel
      * @return \Illuminate\Database\Eloquent\Collection
      */
-    public static function run($channel)
+    public static function get($channel)
     {
         return CommunityLink::with('votes', 'creator', 'channel')
             ->forChannel($channel)

diff --git a/CommunityLinksQuery.php b/CommunityLinksQuery.php
@@ -0,0 +1,31 @@
+<?php
+
+namespace App\Queries;
+
+use App\CommunityLink;
+
+class CommunityLinksQuery
+{
+    /**
+     * Fetch all relevant community links.
+     *
+     * @param  string|null  $channel
+     * @return \Illuminate\Database\Eloquent\Collection
+     */
+    public static function run($channel)
+    {
+        return CommunityLink::with('votes', 'creator', 'channel')
+            ->forChannel($channel)
+            ->where('approved', 1)
+            ->leftJoin(
+                'community_links_votes',
+                'community_links_votes.community_link_id', '=', 'community_links.id'
+            )
+            ->selectRaw(
+                'community_links.*, count(community_links_votes.id) as vote_count'
+            )
+            ->groupBy('community_links.id')
+            ->orderBy('updated_at', 'desc')
+            ->paginate(3);
+    }
+}
diff --git a/LinksController.php b/LinksController.php
@@ -0,0 +1,18 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use App\Queries\CommunityLinksQuery;
+
+class LinksController extends Controller
+{
+    /**
+     * Display all community links.
+     */
+    public function index()
+    {
+        $links = CommunityLinksQuery::get();
+
+        return view('community.links', compact('links'));
+    }
+}