summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-02-19 14:09:12 +0100
committerYorhel <git@yorhel.nl>2022-02-19 14:09:14 +0100
commit33b209a8c90a2bfc8d796c817b3c677402fc4a5e (patch)
tree28113934ed575658ea6de20f30b4dce7502ee5e3
parent43a707c5298409f326ae15b8771e63945a29e5d5 (diff)
Use special "vnt.sorttitle" column for sorting & alphabet filtering
This column simply picks the latin version of the chosen display title, as if the "latin" checkbox is always set.
-rw-r--r--lib/VNWeb/LangPref.pm5
-rw-r--r--lib/VNWeb/ULists/List.pm6
-rw-r--r--lib/VNWeb/VN/List.pm12
-rw-r--r--sql/schema.sql14
-rw-r--r--util/updates/2022-02-19-vnt-sorttitle.sql2
5 files changed, 22 insertions, 17 deletions
diff --git a/lib/VNWeb/LangPref.pm b/lib/VNWeb/LangPref.pm
index 5629b792..b13bcb74 100644
--- a/lib/VNWeb/LangPref.pm
+++ b/lib/VNWeb/LangPref.pm
@@ -59,11 +59,14 @@ sub gen_sql {
my $title = 'COALESCE('.join(',',
map +($_->{latin} ? ($joins{ id($_) }.'.latin') : (), $joins{ id($_) }.'.title'), $p->[0]->@*
).')';
+ my $sorttitle = 'COALESCE('.join(',',
+ map +($joins{ id($_) }.'.latin', $joins{ id($_) }.'.title'), $p->[0]->@*
+ ).')';
my $alttitle = 'COALESCE('.join(',',
(map +($_->{latin} ? ($joins{ id($_) }.'.latin') : (), $joins{ id($_) }.'.title'), $p->[1]->@*), "''"
).')';
- sql "SELECT x.*, $title AS title, $alttitle AS alttitle FROM $tbl_main x", @joins;
+ sql "SELECT x.*, $title AS title, $sorttitle AS sorttitle, $alttitle AS alttitle FROM $tbl_main x", @joins;
}
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index 62040de0..ccd66d0a 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -8,7 +8,7 @@ use VNWeb::Releases::Lib;
my $TABLEOPTS = tableopts
title => {
name => 'Title',
- sort_sql => 'v.title',
+ sort_sql => 'v.sorttitle',
sort_id => 0,
compat => 'title',
sort_default => 'asc',
@@ -270,7 +270,7 @@ sub listing_ {
!$own ? sql('uv.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN(SELECT id FROM ulist_labels WHERE uid =', \$uid, 'AND NOT private))') : (),
@where_vns ? sql_or(@where_vns) : (),
$opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
- defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : ();
+ defined($opt->{ch}) ? sql 'match_firstchar(v.sorttitle, ', \$opt->{ch}, ')' : ();
my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN vnt v ON v.id = uv.vid WHERE', $where);
@@ -282,7 +282,7 @@ sub listing_ {
FROM ulist_vns uv
JOIN vnt v ON v.id = uv.vid
WHERE', $where, '
- ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.title'
+ ORDER BY', $opt->{s}->sql_order(), 'NULLS LAST, v.sorttitle'
);
enrich_flatten labels => id => vid => sql('SELECT vid, lbl FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid IN'), $lst;
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 29a76bd0..5868731b 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -23,7 +23,7 @@ sub TABLEOPTS {
name => 'Title',
compat => 'title',
sort_id => 1,
- sort_sql => 'v.title',
+ sort_sql => 'v.sorttitle',
sort_default => $tags ? undef : 'asc',
},
released => {
@@ -44,7 +44,7 @@ sub TABLEOPTS {
name => 'Popularity score',
compat => 'pop',
sort_id => 3,
- sort_sql => 'v.c_popularity ?o, v.title',
+ sort_sql => 'v.c_popularity ?o, v.sorttitle',
vis_id => 0,
vis_default => 1,
},
@@ -52,20 +52,20 @@ sub TABLEOPTS {
name => 'Bayesian rating',
compat => 'rating',
sort_id => 4,
- sort_sql => 'v.c_rating ?o NULLS LAST, v.title',
+ sort_sql => 'v.c_rating ?o NULLS LAST, v.sorttitle',
vis_id => 1,
vis_default => 1,
},
average => {
name => 'Vote average',
sort_id => 5,
- sort_sql => 'v.c_average ?o NULLS LAST, v.title',
+ sort_sql => 'v.c_average ?o NULLS LAST, v.sorttitle',
vis_id => 3,
},
votes => {
name => 'Number of votes',
sort_id => 6,
- sort_sql => 'v.c_votecount ?o, v.title',
+ sort_sql => 'v.c_votecount ?o, v.sorttitle',
}
}
@@ -264,7 +264,7 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
my $where = sql_and
'NOT v.hidden', $opt->{f}->sql_where(),
$opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
- defined($opt->{ch}) ? sql 'match_firstchar(v.title, ', \$opt->{ch}, ')' : ();
+ defined($opt->{ch}) ? sql 'match_firstchar(v.sorttitle, ', \$opt->{ch}, ')' : ();
my $time = time;
my($count, $list);
diff --git a/sql/schema.sql b/sql/schema.sql
index 1d6d0e5e..13e5f20f 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1252,10 +1252,10 @@ CREATE TABLE wikidata (
);
--- The 'vnt' view is equivalent to the 'vn' table with two additional columns:
--- 'title' and 'alttitle', which represent the display title and the
--- alternative (mouse-hover) title. The view defined here displays the
--- latin/title name of the original language as main title and the title in the
--- original script as alttitle, but this view can be redefined as a TEMPORARY
--- VIEW in sessions to override the default behavior.
-CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;
+-- The 'vnt' view is equivalent to the 'vn' table with three additional columns:
+-- title - main display title
+-- sorttitle - title used for sorting and alphabet filter (i.e. latin version of 'title')
+-- alttitle - alternative display title (for e.g. tooltips)
+-- This view can be redefined as a TEMPORARY VIEW in sessions to override the
+-- default behavior.
+CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, COALESCE(vo.latin, vo.title) AS sorttitle, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;
diff --git a/util/updates/2022-02-19-vnt-sorttitle.sql b/util/updates/2022-02-19-vnt-sorttitle.sql
new file mode 100644
index 00000000..ea075343
--- /dev/null
+++ b/util/updates/2022-02-19-vnt-sorttitle.sql
@@ -0,0 +1,2 @@
+DROP VIEW vnt;
+CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, COALESCE(vo.latin, vo.title) AS sorttitle, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang;