Changes database view.

2 files changed, 23 insertions, 2 deletions

diff --git a/database.go b/database.go
index c6434ff..99fa2a7 100644
--- a/database.go
+++ b/database.go
@@ -54,12 +54,12 @@ CREATE TABLE IF NOT EXISTS entry (
 ) CHARSET=utf8;
 	`,
 		`
-		CREATE OR REPLACE VIEW view_entry AS
+		CREATE OR REPLACE VIEW entry_view AS
 SELECT
         entry.id,
         title,
         synopsis,
-        owner.name,
+        owner.name AS owner,
         platform.name AS platform,
         platform.url AS platform_url,
         CONCAT( platform.url, entry.url) AS url,
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;