Changes database view.

1 files changed, 21 insertions, 0 deletions

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;