Initial commit.

1 files changed, 53 insertions, 0 deletions

diff --git a/database/migrations/2020_03_28_132812_create_view_articles.php b/database/migrations/2020_03_28_132812_create_view_articles.php
new file mode 100644
index 0000000..15fdee8
--- /dev/null
+++ b/database/migrations/2020_03_28_132812_create_view_articles.php
@@ -0,0 +1,53 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\DB;
+
+class CreateViewArticles extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+	DB::statement(
+		"CREATE VIEW view_article 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"
+	);
+    }
+
+    /**
+     * Reverse the migrations.
+     *
+     * @return void
+     */
+    public function down()
+    {
+	DB::statement("DROP VIEW view_article;");
+    }
+}