diff options
author | Yorhel <git@yorhel.nl> | 2021-06-04 09:38:19 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-06-04 09:38:21 +0200 |
commit | 7ff0f4b68b402155b92097f7a53e52a5d51ff3db (patch) | |
tree | 3f2e2bdd78d28ce71cbbe4fa6707a20da2ee3781 /lib | |
parent | 5443cbeb23d9c6a3b63aafaff4898377d33165f4 (diff) |
VN listing: Add table options + developer and raw average columns
With additional VN cache columns for the new developers and average
table columns. The developers cache is also used by the AdvSearch to
potentially speed up some queries (and slow down others).
I also changed the popularity and rating caches to smallint. Doesn't
save anything with the current padding, but there's not much point in
using a floating point type when the values get rounded anyway.
Diffstat (limited to 'lib')
-rw-r--r-- | lib/VNWeb/AdvSearch.pm | 10 | ||||
-rw-r--r-- | lib/VNWeb/HTML.pm | 14 | ||||
-rw-r--r-- | lib/VNWeb/TT/TagPage.pm | 36 | ||||
-rw-r--r-- | lib/VNWeb/TableOpts.pm | 73 | ||||
-rw-r--r-- | lib/VNWeb/ULists/List.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/VN/List.pm | 152 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 4 |
7 files changed, 200 insertions, 91 deletions
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm index e46b8e53..b1c58062 100644 --- a/lib/VNWeb/AdvSearch.pm +++ b/lib/VNWeb/AdvSearch.pm @@ -311,10 +311,11 @@ my @TYPE; # stack of query types, $TYPE[0] is the top-level query, $TYPE[$#TYPE] f v => 2 => 'lang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_languages && ARRAY', \$_, '::language[]' }; f v => 3 => 'olang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.olang =', \$_ }; f v => 4 => 'platform', { enum => \%PLATFORM }, '=' => sub { sql 'v.c_platforms && ARRAY', \$_, '::platform[]' }; +f v => 6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'v.c_developers && ARRAY', \$_, '::vndbid[]' }; f v => 5 => 'length', { uint => 1, enum => \%VN_LENGTH }, '=' => sub { sql 'v.length =', \$_ }; f v => 7 => 'released', { fuzzyrdate => 1 }, sql => sub { sql 'v.c_released', $_[0], \($_ == 1 ? strftime('%Y%m%d', gmtime) : $_) }; f v => 9 => 'popularity',{ uint => 1, range => [ 0, 100] }, sql => sub { sql 'v.c_popularity', $_[0], \($_/100) }; -f v => 10 => 'rating', { uint => 1, range => [10, 100] }, sql => sub { sql 'v.c_rating <> 0 AND v.c_rating', $_[0], \$_ }; +f v => 10 => 'rating', { uint => 1, range => [10, 100] }, sql => sub { sql 'v.c_rating', $_[0], \$_ }; f v => 11 => 'vote-count',{ uint => 1, range => [ 0,1<<30] }, sql => sub { sql 'v.c_votecount', $_[0], \$_ }; f v => 61 => 'has-description', { uint => 1, range => [1,1] }, '=' => sub { 'v."desc" <> \'\'' }; f v => 62 => 'has-anime', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' }; @@ -322,13 +323,6 @@ f v => 63 => 'has-screenshot', { uint => 1, range => [1,1] }, '=' => sub { 'EXI f v => 64 => 'has-review', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM reviews r WHERE r.vid = v.id AND NOT r.c_flagged)' }; f v => 65 => 'on-list', { uint => 1, range => [1,1] }, '=' => sub { auth ? sql 'v.id IN(SELECT vid FROM ulist_vns WHERE uid =', \auth->uid, ')' : '1=0' }; -f v => 6 => 'developer-id',{ vndbid => 'p' }, - sql_list => sub { - my($neg, $all, $val) = @_; - sql 'v.id', $neg ? 'NOT' : '', 'IN(SELECT rv.vid FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_producers rp ON rp.id = r.id - WHERE NOT r.hidden AND rp.developer AND rp.pid IN', $val, $all && @$val > 1 ? ('GROUP BY rv.vid HAVING COUNT(rp.pid) =', \scalar @$val) : (), ')'; - }; - f v => 8 => 'tag', { type => 'any', func => \&_validate_tag }, compact => sub { my $id = ($_->[0] =~ s/^g//r)*1; $_->[1] == 0 && $_->[2] == 0 ? $id : [ $id, int($_->[2]*5)*3 + $_->[1] ] }, sql_list => \&_sql_where_tag; diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm index 8ec6ea85..58f0cf22 100644 --- a/lib/VNWeb/HTML.pm +++ b/lib/VNWeb/HTML.pm @@ -806,14 +806,20 @@ sub paginate_ { # Generate sort buttons for a table header. This function assumes that sorting -# options are given as query parameters: 's' for the $column_name to sort on -# and 'o' for order ('a'sc/'d'esc). +# options are given either as a TableOpts parameter in 's' or as two query +# parameters: 's' for the $column_name to sort on and 'o' for order ('a'/'d'). # Options: $column_title, $column_name, $opt, $url # Where $url is a function that is given ('p', undef, 's', $column_name, 'o', $order) and returns a URL. sub sortable_ { my($name, $opt, $url) = @_; - $opt->{s} eq $name && $opt->{o} eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $name, o => 'a'), ' ▴'; - $opt->{s} eq $name && $opt->{o} eq 'd' ? txt_ '▾' : a_ href => $url->(p => undef, s => $name, o => 'd'), '▾'; + if(ref $opt->{s}) { + my $o = $opt->{s}->sorted($name); + $o eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $opt->{s}->sort_param($name, 'a')), ' ▴'; + $o eq 'd' ? txt_ '▾' : a_ href => $url->(p => undef, s => $opt->{s}->sort_param($name, 'd')), '▾'; + } else { + $opt->{s} eq $name && $opt->{o} eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $name, o => 'a'), ' ▴'; + $opt->{s} eq $name && $opt->{o} eq 'd' ? txt_ '▾' : a_ href => $url->(p => undef, s => $name, o => 'd'), '▾'; + } } diff --git a/lib/VNWeb/TT/TagPage.pm b/lib/VNWeb/TT/TagPage.pm index e7b63f31..9be519ff 100644 --- a/lib/VNWeb/TT/TagPage.pm +++ b/lib/VNWeb/TT/TagPage.pm @@ -65,14 +65,16 @@ sub infobox_ { } +my $TABLEOPTS = VNWeb::VN::List::TABLEOPTS(1); + + sub vns_ { my($t) = @_; my $opt = tuwf->validate(get => p => { upage => 1 }, f => { advsearch_err => 'v' }, - s => { onerror => 'tagscore', enum => [qw/tagscore title rel pop rating/] }, - o => { onerror => 'd', enum => ['a','d'] }, + s => { tableopts => $TABLEOPTS }, m => { onerror => [auth->pref('spoilers')||0], type => 'array', scalar => 1, minlength => 1, values => { enum => [0..2] } }, fil => { required => 0 }, )->data; @@ -99,44 +101,36 @@ sub vns_ { my($count, $list); db_maytimeout { $count = tuwf->dbVali('SELECT count(*) FROM vn v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where); - $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, ' - SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating + $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, ' + SELECT tvi.rating AS tagscore, v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average , v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang FROM vn v JOIN tags_vn_inherit tvi ON tvi.vid = v.id WHERE', $where, ' - ORDER BY', sprintf { - tagscore => 'tvi.rating %s, v.title', - title => 'v.title %s', - rel => 'v.c_released %s, v.title', - pop => 'v.c_popularity %s NULLS LAST, v.title', - rating => 'v.c_rating %s NULLS LAST, v.title' - }->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC' + ORDER BY', $opt->{s}->sql_order(), ) : []; } || (($count, $list) = (undef, [])); - VNWeb::VN::List::enrich_userlist $list; + VNWeb::VN::List::enrich_listing $opt, $list; $time = time - $time; - div_ class => 'mainbox', sub { - p_ class => 'mainopts', sub { - a_ href => "/g/links?t=$t->{id}", 'Recently tagged'; - }; - h1_ 'Visual novels'; - form_ action => "/$t->{id}", method => 'get', sub { + form_ action => "/$t->{id}", method => 'get', sub { + div_ class => 'mainbox', sub { + p_ class => 'mainopts', sub { + a_ href => "/g/links?t=$t->{id}", 'Recently tagged'; + }; + h1_ 'Visual novels'; p_ class => 'browseopts', sub { button_ type => 'submit', name => 'm', value => 0, $opt->{m} == 0 ? (class => 'optselected') : (), 'Hide spoilers'; button_ type => 'submit', name => 'm', value => 1, $opt->{m} == 1 ? (class => 'optselected') : (), 'Show minor spoilers'; button_ type => 'submit', name => 'm', value => 2, $opt->{m} == 2 ? (class => 'optselected') : (), 'Spoil me!'; }; - input_ type => 'hidden', name => 'o', value => $opt->{o}; - input_ type => 'hidden', name => 's', value => $opt->{s}; input_ type => 'hidden', name => 'm', value => $opt->{m}; $opt->{f}->elm_; advsearch_msg_ $count, $time; }; + VNWeb::VN::List::listing_ $opt, $list, $count, 1 if $count; }; - VNWeb::VN::List::listing_ $opt, $list, $count, 1 if $count; } diff --git a/lib/VNWeb/TableOpts.pm b/lib/VNWeb/TableOpts.pm index 6d6384fb..432bf548 100644 --- a/lib/VNWeb/TableOpts.pm +++ b/lib/VNWeb/TableOpts.pm @@ -43,11 +43,11 @@ package VNWeb::TableOpts; # # my $opts = tuwf->validate(get => s => { tableopts => $config })->data; # -# my $sql = sql('.... ORDER BY', $opts->sql_order); (TODO) +# my $sql = sql('.... ORDER BY', $opts->sql_order); # # $opts->view; # Current view, 'rows', 'cards' or 'grid' # $opts->results; # How many results to display -# $opts->vis('popularity'); # is the column visible? (TODO) +# $opts->vis('popularity'); # is the column visible? # # # @@ -89,9 +89,8 @@ my %results = map +($results[$_], $_), 0..$#results; # Turn config options into something more efficient to work with sub tableopts { my %o = ( - sort_ids => [], # identifier => column name - vis_ids => [], # identifier => column name - col_order => [], # column names in the order listed in the config + sort_ids => [], # identifier => column config hash + col_order => [], # column config hashes in the order listed in the config columns => {}, # column name => config hash views => [], # supported views, as numbers default => 0, # default settings, integer form @@ -107,9 +106,9 @@ sub tableopts { next; } $o{columns}{$k} = $v; - push $o{col_order}->@*, $k; - $o{sort_ids}[$v->{sort_id}] = $k if defined $v->{sort_id}; - $o{vis_ids}[$v->{vis_id}] = $k if defined $v->{vis_id}; + $v->{id} = $k; + push $o{col_order}->@*, $v; + $o{sort_ids}[$v->{sort_id}] = $v if defined $v->{sort_id}; $o{default} |= ($v->{sort_id} << 6) | ({qw|asc 0 desc 32|}->{$v->{sort_default}}//croak("unknown sort_default: $v->{sort_default}")) if $v->{sort_default}; $o{default} |= 1 << ($v->{vis_id} + 12) if $v->{vis_default}; } @@ -125,10 +124,18 @@ TUWF::set('custom_validations')->{tableopts} = sub { my $d = $t->{pref} && auth ? tuwf->dbVali('SELECT', $t->{pref}, 'FROM users WHERE id =', \auth->uid) : undef; bless([$d // $t->{default},$t], __PACKAGE__) }, func => sub { - # TODO: compatibility with the old ?s=<colname> sort parameter - my $v = _dec($_[0]) // return 0; + my $obj = bless [undef, $t], __PACKAGE__; + my $col = [grep $_->{compat} && $_->{compat} eq $_[0], values $t->{columns}->%*]->[0]; + if($col && defined $col->{sort_id}) { + $obj->[0] = $t->{default}; + $obj->set_sort_col_id($col->{sort_id}); + my $ord = tuwf->reqGet('o'); + $obj->set_order($ord && $ord eq 'd' ? 1 : 0); + } else { + $obj->[0] = _dec($_[0]) // return 0; + } + $_[0] = $obj; # We could do strict validation on the individual fields, but the methods below can handle incorrect data. - $_[0] = bless [$v, $t], __PACKAGE__; 1; } } }; @@ -145,17 +152,55 @@ sub grid { shift->view eq 'grid' } sub results { $results[($_[0][0] >> 2) & 7] || $results[0] } +sub order { $_[0][0] & 32 } +sub set_order { if($_[1]) { $_[0][0] |= 32 } else { $_[0][0] &= ~32 } } + +sub sort_col_id { ($_[0][0] >> 6) & 63 } +sub set_sort_col_id { $_[0][0] = ($_[0][0] & (~1 - 0b111111000000)) | ($_[1] << 6) } + +# Given the key of a column, returns whether it is currently sorted on ('' / 'a' / 'd') +sub sorted { + my($self, $key) = @_; + $self->[1]{columns}{$key}{sort_id} != $self->sort_col_id ? '' : $self->order ? 'd' : 'a'; +} + +# Given the key of a column and the desired order ('a'/'d'), returns a new object with that sorting applied. +sub sort_param { + my($self, $key, $o) = @_; + my $n = bless [@$self], __PACKAGE__; + $n->set_order($o eq 'a' ? 0 : 1); + $n->set_sort_col_id($self->[1]{columns}{$key}{sort_id}); + $n +} + +# Returns an SQL expression suitable for use in an ORDER BY clause. +sub sql_order { + my($self) = @_; + my($v,$o) = $self->@*; + my $col = $o->{sort_ids}[ $self->sort_col_id ] || $o->{sort_ids}[ sort_col_id([$o->{default}]) ]; + die "No column to sort on" if !$col; + my $order = $self->order ? 'DESC' : 'ASC'; + my $opposite_order = $self->order ? 'ASC' : 'DESC'; + my $sql = $col->{sort_sql}; + $sql =~ /[?!]o/ ? ($sql =~ s/\?o/$order/rg =~ s/!o/$opposite_order/rg) : "$sql $order"; +} + + +# Returns whether the given column key is visible. +sub vis { $_[0][0] & (1 << (12+$_[0][1]{columns}{$_[1]}{vis_id})) } + my $FORM_OUT = form_compile any => { save => { required => 0 }, views => { type => 'array', values => { uint => 1 } }, default => { uint => 1 }, value => { uint => 1 }, - # TODO: Sorting & column visibility + sorts => { aoh => { id => { uint => 1 }, name => {} } }, + vis => { aoh => { id => { uint => 1 }, name => {} } }, }; elm_api TableOptsSave => $FORM_OUT, { - save => { enum => ['tableopts_c'] }, + save => { enum => ['tableopts_c', 'tableopts_v', 'tableopts_vt'] }, value => { required => 0, uint => 1 } }, sub { my($f) = @_; @@ -172,6 +217,8 @@ sub elm_ { views => $o->{views}, default => $o->{default}, value => $v, + sorts => [ map +{ id => $_->{sort_id}, name => $_->{name} }, grep defined $_->{sort_id}, values $o->{col_order}->@* ], + vis => [ map +{ id => $_->{vis_id}, name => $_->{name} }, grep defined $_->{vis_id}, values $o->{col_order}->@* ], }, sub { TUWF::XML::input_ type => 'hidden', name => 's', value => $self->query_encode if defined $self->query_encode }; diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm index ae443955..fc0bbf9e 100644 --- a/lib/VNWeb/ULists/List.pm +++ b/lib/VNWeb/ULists/List.pm @@ -117,7 +117,7 @@ sub vn_ { } if in vote => $opt->{c}; td_ class => 'tc_rating', sub { - txt_ sprintf '%.2f', ($v->{c_rating}||0)/10; + txt_ sprintf '%.2f', ($v->{c_rating}||0)/100; b_ class => 'grayedout', sprintf ' (%d)', $v->{c_votecount}; } if in rating => $opt->{c}; diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm index 422e8b1b..aab698b7 100644 --- a/lib/VNWeb/VN/List.pm +++ b/lib/VNWeb/VN/List.pm @@ -5,6 +5,64 @@ use VNWeb::AdvSearch; use VNWeb::Filters; use VNWeb::TT::Lib 'tagscore_'; +# Returns the tableopts config for this VN list (0) or the VN listing on tags (1). +sub TABLEOPTS { + my($tags) = @_; + tableopts _pref => $tags ? 'tableopts_vt' : 'tableopts_v', + $tags ? (tagscore => { + name => 'Tag score', + compat => 'tagscore', + sort_id => 0, + sort_sql => 'tvi.rating ?o, v.title', + sort_default => 'desc' + }) : (), + title => { + name => 'Title', + compat => 'title', + sort_id => 1, + sort_sql => 'v.title', + sort_default => $tags ? undef : 'asc', + }, + released => { + name => 'Release date', + compat => 'rel', + sort_id => 2, + sort_sql => 'v.c_released ?o, v.title', + }, + developer => { + name => 'Developer', + vis_id => 2, + }, + popularity => { + name => 'Popularity score', + compat => 'pop', + sort_id => 3, + sort_sql => 'v.c_popularity ?o NULLS LAST, v.title', + vis_id => 0, + vis_default => 1, + }, + rating => { + name => 'Bayesian rating', + compat => 'rating', + sort_id => 4, + sort_sql => 'v.c_rating ?o NULLS LAST, v.title', + vis_id => 1, + vis_default => 1, + }, + average => { + name => 'Vote average', + sort_id => 5, + sort_sql => 'v.c_average ?o NULLS LAST, v.title', + vis_id => 3, + }, + votes => { + name => 'Number of votes', + sort_id => 6, + sort_sql => 'v.c_votecount ?o, v.title', + } +} + +my $TABLEOPTS = TABLEOPTS 0; # Also used by VNWeb::TT::TagPage sub listing_ { @@ -12,46 +70,63 @@ sub listing_ { my sub url { '?'.query_encode %$opt, @_ } - paginate_ \&url, $opt->{p}, [$count, 50], 't'; + paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 't', sub { $opt->{s}->elm_ }; div_ class => 'mainbox browse vnbrowse', sub { table_ class => 'stripe', sub { thead_ sub { tr_ sub { - td_ class => 'tc_s',sub { txt_ 'Score'; sortable_ 'tagscore', $opt, \&url } if $tagscore; - td_ class => $tagscore ? 'tc_t' : 'tc1', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url }; - td_ class => 'tc7', ''; - td_ class => 'tc2', ''; - td_ class => 'tc3', ''; - td_ class => 'tc4', sub { txt_ 'Released'; sortable_ 'rel', $opt, \&url }; - td_ class => 'tc5', sub { txt_ 'Popularity'; sortable_ 'pop', $opt, \&url }; - td_ class => 'tc6', sub { txt_ 'Rating'; sortable_ 'rating', $opt, \&url }; + td_ class => 'tc_score', sub { txt_ 'Score'; sortable_ 'tagscore', $opt, \&url } if $tagscore; + td_ class => 'tc_title', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url }; + td_ class => 'tc_dev', 'Developer' if $opt->{s}->vis('developer'); + td_ class => 'tc_ulist', ''; + td_ class => 'tc_plat', ''; + td_ class => 'tc_lang', ''; + td_ class => 'tc_rel', sub { txt_ 'Released'; sortable_ 'released', $opt, \&url }; + td_ class => 'tc_pop', sub { txt_ 'Popularity'; sortable_ 'popularity', $opt, \&url } if $opt->{s}->vis('popularity'); + td_ class => 'tc_rating',sub { txt_ 'Rating'; sortable_ 'rating', $opt, \&url } if $opt->{s}->vis('rating'); + td_ class => 'tc_average',sub{ txt_ 'Average'; sortable_ 'average', $opt, \&url } if $opt->{s}->vis('average'); } }; tr_ sub { - td_ class => 'tc_s',sub { tagscore_ $_->{tagscore} } if $tagscore; - td_ class => $tagscore ? 'tc_t' : 'tc1', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }; - td_ class => 'tc7', sub { + td_ class => 'tc_score', sub { tagscore_ $_->{tagscore} } if $tagscore; + td_ class => 'tc_title', sub { a_ href => "/$_->{id}", title => $_->{original}||$_->{title}, $_->{title} }; + td_ class => 'tc_dev', sub { + join_ ' & ', sub { + a_ href => "/$_->{id}", title => $_->{original}||$_->{name}, $_->{name}; + }, sort { $a->{name} cmp $b->{name} || $a->{id} <=> $b->{id} } $_->{developers}->@*; + } if $opt->{s}->vis('developer'); + td_ class => 'tc_ulist', sub { b_ class => $_->{userlist_obtained} == $_->{userlist_all} ? 'done' : 'todo', sprintf '%d/%d', $_->{userlist_obtained}, $_->{userlist_all} if $_->{userlist_all}; abbr_ title => join(', ', $_->{vnlist_labels}->@*), scalar $_->{vnlist_labels}->@* if $_->{vnlist_labels} && $_->{vnlist_labels}->@*; abbr_ title => 'No labels', ' ' if $_->{vnlist_labels} && !$_->{vnlist_labels}->@*; }; - td_ class => 'tc2', sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* }; - td_ class => 'tc3', sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* }; - td_ class => 'tc4', sub { rdate_ $_->{c_released} }; - td_ class => 'tc5', sprintf '%.2f', ($_->{c_popularity}||0)*100; - td_ class => 'tc6', sub { - txt_ sprintf '%.2f', ($_->{c_rating}||0)/10; + td_ class => 'tc_plat', sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* }; + td_ class => 'tc_lang', sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* }; + td_ class => 'tc_rel', sub { rdate_ $_->{c_released} }; + td_ class => 'tc_pop', sprintf '%.2f', ($_->{c_popularity}||0)/100 if $opt->{s}->vis('popularity'); + td_ class => 'tc_rating',sub { + txt_ sprintf '%.2f', ($_->{c_rating}||0)/100; b_ class => 'grayedout', sprintf ' (%d)', $_->{c_votecount}; - }; + } if $opt->{s}->vis('rating'); + td_ class => 'tc_average',sub { + txt_ sprintf '%.2f', ($_->{c_average}||0)/100; + b_ class => 'grayedout', sprintf ' (%d)', $_->{c_votecount} if !$opt->{s}->vis('rating'); + } if $opt->{s}->vis('average'); } for @$list; } }; - paginate_ \&url, $opt->{p}, [$count, 50], 'b'; + paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 'b'; } -# Enrich the userlist fields needed for listing_() +# Enrich some extra fields fields needed for listing_() # Also used by VNWeb::TT::TagPage -sub enrich_userlist { - return if !auth; +sub enrich_listing { + my $opt = shift; + + enrich developers => id => vid => sub { + 'SELECT v.id AS vid, p.id, p.name, p.original + FROM vn v, unnest(v.c_developers) vp(id), producers p + WHERE p.id = vp.id AND v.id IN', $_[0], 'ORDER BY p.name, p.id' + }, @_ if $opt->{s}->vis('developer'); enrich_merge id => sub { sql ' SELECT irv.vid AS id @@ -61,7 +136,7 @@ sub enrich_userlist { JOIN releases_vn irv ON irv.id = irl.rid WHERE irl.uid =', \auth->uid, 'AND irv.vid IN', $_, ' GROUP BY irv.vid - ' }, @_; + ' }, @_ if auth; enrich_flatten vnlist_labels => id => vid => sub { sql ' SELECT uvl.vid, ul.label @@ -69,7 +144,7 @@ sub enrich_userlist { JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid =', \auth->uid, 'AND uvl.vid IN', $_[0], ' ORDER BY CASE WHEN ul.id < 10 THEN ul.id ELSE 10 END, ul.label' - }, @_; + }, @_ if auth; } @@ -79,8 +154,7 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub { sq=> { onerror => undef }, p => { upage => 1 }, f => { advsearch_err => 'v' }, - s => { onerror => 'title', enum => [qw/title rel pop rating/] }, - o => { onerror => 'a', enum => ['a','d'] }, + s => { tableopts => $TABLEOPTS }, ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } }, fil => { required => 0 }, rfil => { required => 0 }, @@ -121,41 +195,35 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub { my($count, $list); db_maytimeout { $count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where); - $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, ' - SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang + $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, ' + SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average + , v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang FROM vn v WHERE', $where, ' - ORDER BY', sprintf { - title => 'v.title %s', - rel => 'v.c_released %s, v.title', - pop => 'v.c_popularity %s NULLS LAST, v.title', - rating => 'v.c_rating %s NULLS LAST, v.title' - }->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC' + ORDER BY', $opt->{s}->sql_order(), ) : []; } || (($count, $list) = (undef, [])); return tuwf->resRedirect("/$list->[0]{id}") if $count && $count == 1 && $opt->{q} && !defined $opt->{ch}; - enrich_userlist $list; + enrich_listing($opt, $list); $time = time - $time; framework_ title => 'Browse visual novels', sub { - div_ class => 'mainbox', sub { - h1_ 'Browse visual novels'; - form_ action => '/v', method => 'get', sub { + form_ action => '/v', method => 'get', sub { + div_ class => 'mainbox', sub { + h1_ 'Browse visual novels'; searchbox_ v => $opt->{q}//''; p_ class => 'browseopts', sub { button_ type => 'submit', name => 'ch', value => ($_//''), ($_//'') eq ($opt->{ch}//'') ? (class => 'optselected') : (), !defined $_ ? 'ALL' : $_ ? uc $_ : '#' for (undef, 'a'..'z', 0); }; - input_ type => 'hidden', name => 'o', value => $opt->{o}; - input_ type => 'hidden', name => 's', value => $opt->{s}; input_ type => 'hidden', name => 'ch', value => $opt->{ch}//''; $opt->{f}->elm_; advsearch_msg_ $count, $time; }; + listing_ $opt, $list, $count if $count; }; - listing_ $opt, $list, $count if $count; }; }; diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index 68239338..32e4a567 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -671,8 +671,8 @@ sub stats_ { clearfloat_; div_ sub { h3_ 'Ranking'; - p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{c_pop_rank}, $rank->{c_popularity}*100 if defined $rank->{c_popularity}; - p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{c_rat_rank}, $rank->{c_rating}/10; + p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{c_pop_rank}, $rank->{c_popularity}/100 if defined $rank->{c_popularity}; + p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{c_rat_rank}, $rank->{c_rating}/100; } if $v->{c_votecount}; } |