From 5a8c47e29afdbb61c32c1e03162abb1bb871ee9e Mon Sep 17 00:00:00 2001
From: horus
Date: Thu, 2 Apr 2020 21:52:04 +0200
Subject: Initial commit.

---
 .../2020_03_28_132812_create_view_articles.php     | 53 ++++++++++++++++++++++
 1 file changed, 53 insertions(+)
 create mode 100644 database/migrations/2020_03_28_132812_create_view_articles.php

(limited to 'database/migrations/2020_03_28_132812_create_view_articles.php')

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;");
+    }
+}
-- 
cgit v1.2.3