diff options
author | Yorhel <git@yorhel.nl> | 2022-02-19 14:09:12 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2022-02-19 14:09:14 +0100 |
commit | 33b209a8c90a2bfc8d796c817b3c677402fc4a5e (patch) | |
tree | 28113934ed575658ea6de20f30b4dce7502ee5e3 | |
parent | 43a707c5298409f326ae15b8771e63945a29e5d5 (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.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/ULists/List.pm | 6 | ||||
-rw-r--r-- | lib/VNWeb/VN/List.pm | 12 | ||||
-rw-r--r-- | sql/schema.sql | 14 | ||||
-rw-r--r-- | util/updates/2022-02-19-vnt-sorttitle.sql | 2 |
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; |