summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-09-20 08:45:03 +0200
committerYorhel <git@yorhel.nl>2020-09-20 08:45:05 +0200
commita757446ea9b6511338889dbc5409b9fea77ed779 (patch)
treeefd43c8885fbf621def3c8ba97974a7706d4fb18
parent509f0efc1ab11c187bc4b35980abd692306a6697 (diff)
SQL: Fix vn.c_lang when multiple first releases on the same date
https://vndb.org/t2520.485
-rw-r--r--sql/func.sql30
1 files changed, 18 insertions, 12 deletions
diff --git a/sql/func.sql b/sql/func.sql
index 6cb3735d..6b77d30a 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -61,18 +61,24 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
GROUP BY rv.vid
), 0),
c_olang = ARRAY(
- SELECT lang
- FROM releases_lang
- WHERE id = (
- SELECT r.id
- FROM releases_vn rv
- JOIN releases r ON rv.id = r.id
- WHERE r.released > 0
- AND NOT r.hidden
- AND rv.vid = $1
- ORDER BY r.released
- LIMIT 1
- )
+ SELECT rl.lang
+ FROM releases_lang rl
+ JOIN releases r ON r.id = rl.id
+ JOIN releases_vn rv ON r.id = rv.id
+ WHERE rv.vid = $1
+ AND NOT r.hidden
+ AND r.released > 0
+ AND NOT EXISTS(
+ SELECT 1
+ FROM releases r2
+ JOIN releases_vn rv2 ON r2.id = rv2.id
+ WHERE rv2.vid = $1
+ AND NOT r2.hidden
+ AND r2.released > 0
+ AND r2.released < r.released
+ )
+ GROUP BY rl.lang
+ ORDER BY rl.lang
),
c_languages = ARRAY(
SELECT rl.lang