1 files changed, 55 insertions, 0 deletions
diff --git a/database/migrations/2020_03_28_131201_create_view_popular_articles.php b/database/migrations/2020_03_28_131201_create_view_popular_articles.php
new file mode 100644
index 0000000..062b8e0
--- /dev/null
+++ b/database/migrations/2020_03_28_131201_create_view_popular_articles.php
@@ -0,0 +1,55 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\DB;
+
+class CreateViewPopularArticles extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+	DB::statement("
+		CREATE VIEW view_popular AS
+		SELECT 
+			article_id as id,
+			article_id,
+			a.created_at,
+			a.updated_at,
+			a.url,
+			a.title,
+			a.excerpt_html,
+			sum(upvotes)+sum(comments) AS impact,
+			sum(upvotes) AS upvotes,
+			sum(comments) as comments,
+			count(article_id) AS repost
+		FROM 
+			discussion as d 
+		JOIN 
+			article AS a ON d.article_id = a.id 
+		GROUP BY 
+			article_id, 
+			a.created_at,
+			a.updated_at,
+			a.url,
+			a.title,
+			a.excerpt_html
+			HAVING (impact > 300) OR (count(article_id) > 2) 
+		ORDER BY ((sum(upvotes)+sum(comments))*count(article_id)) desc, repost desc, impact desc"
+	);
+    }
+
+    /**
+     * Reverse the migrations.
+     *
+     * @return void
+     */
+    public function down()
+    {
+	DB::statement("DROP VIEW view_popular;");
+    }
+}