From 3f5eaaaaedee0ef41719fa2f6a8e02446484d785 Mon Sep 17 00:00:00 2001
From: horus
Date: Sun, 21 Apr 2019 14:16:43 +0200
Subject: Changes database view.

---
 schema.sql | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 7048798..c54c838 100644
--- a/schema.sql
+++ b/schema.sql
@@ -38,3 +38,24 @@ CREATE TABLE IF NOT EXISTS entry (
 	CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
 	CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
 ) CHARSET=utf8;
+
+CREATE OR REPLACE VIEW entry_view AS
+SELECT
+        entry.id,
+        title,
+        synopsis,
+        owner.name AS owner,
+        platform.name AS platform,
+        platform.url AS platform_url,
+        CONCAT( platform.url, entry.url) AS url,
+        language.name AS language,
+        stars,
+        created_at
+FROM entry
+JOIN owner ON
+        entry.owner = owner.id
+JOIN platform on
+        entry.platform = platform.id
+JOIN language
+        ON entry.language = language.id
+ORDER BY created_at DESC;
-- 
cgit v1.2.3

