diff options
author | Yorhel <git@yorhel.nl> | 2019-12-22 16:51:50 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-12-22 16:54:42 +0100 |
commit | 0916ec2e23f7c15a484781894ee0edee728ecd13 (patch) | |
tree | 78ba8b0ea4f4ecceaea6c5e5562d89079b50800e | |
parent | cd5e4dffdf4d99cac7d47433981cfa6d669b2b45 (diff) |
ulist: Update "My lists" VN filters to use new ulists feature
This is just a simple backwards-compatible port, proper integration
should allow one to filter on individual labels. The "On my VN list"
filter now works slightly differently in that it considers *everything*
in ulist_vns to be on the users' VN list, including votes and wishlist.
I've removed the option to display a "Wishlist" column. The wishlist
works rather differently now, maybe this should instead just display all
assigned labels - but that would consume quite a bit of space. :(
-rw-r--r-- | lib/VNDB/DB/VN.pm | 15 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 7 | ||||
-rw-r--r-- | util/sql/schema.sql | 2 |
3 files changed, 8 insertions, 16 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index c3ec2cc2..afc64282 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -14,8 +14,8 @@ our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotA # Options: id, char, search, gtin, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, # hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, # reverse, inc_hidden, date_before, date_after, released, release, character -# What: extended anime staff seiyuu relations screenshots relgraph rating ranking wishlist vnlist -# Note: wishlist and vnlist are ignored (no db search) unless a user is logged in +# What: extended anime staff seiyuu relations screenshots relgraph rating ranking vnlist +# Note: vnlist is ignored (no db search) unless a user is logged in # Sort: id rel pop rating title tagscore rand sub dbVNGet { my($self, %o) = @_; @@ -65,13 +65,13 @@ sub dbVNGet { $o{staff_inc} ? ( 'v.id IN(SELECT ivs.id FROM vn_staff ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{staff_inc} ? $o{staff_inc} : [$o{staff_inc}] ] ) : (), $o{staff_exc} ? ( 'v.id NOT IN(SELECT ivs.id FROM vn_staff ivs JOIN staff_alias isa ON isa.aid = ivs.aid WHERE isa.id IN(!l))' => [ ref $o{staff_exc} ? $o{staff_exc} : [$o{staff_exc}] ] ) : (), $uid && $o{ul_notblack} ? ( - 'v.id NOT IN(SELECT vid FROM wlists WHERE uid = ? AND wstat = 3)' => $uid ) : (), + 'v.id NOT IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 6)' => $uid ) : (), $uid && defined $o{ul_onwish} ? ( - 'v.id !s IN(SELECT vid FROM wlists WHERE uid = ?)' => [ $o{ul_onwish} ? '' : 'NOT', $uid ] ) : (), + 'v.id !s IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 5)' => [ $o{ul_onwish} ? '' : 'NOT', $uid ] ) : (), $uid && defined $o{ul_voted} ? ( - 'v.id !s IN(SELECT vid FROM votes WHERE uid = ?)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (), + 'v.id !s IN(SELECT vid FROM ulist_vns_labels WHERE uid = ? AND lbl = 7)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (), $uid && defined $o{ul_onlist} ? ( - 'v.id !s IN(SELECT vid FROM vnlists WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (), + 'v.id !s IN(SELECT vid FROM ulist_vns WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (), !$o{id} && !$o{inc_hidden} ? ( 'v.hidden = FALSE' => 0 ) : (), # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well) @@ -97,8 +97,6 @@ sub dbVNGet { my @join = ( $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), - $uid && $o{what} =~ /wishlist/ ? - 'LEFT JOIN wlists wl ON wl.vid = v.id AND wl.uid = ' . $uid : (), $uid && $o{what} =~ /vnlist/ ? ("LEFT JOIN ( SELECT irv.vid, COUNT(*) AS userlist_all, SUM(CASE WHEN irl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained @@ -120,7 +118,6 @@ sub dbVNGet { '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(v.c_popularity, 0.0)) AS p_ranking', '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(v.c_rating, 0.0)) AS r_ranking', ) : (), - $uid && $o{what} =~ /wishlist/ ? 'wl.wstat' : (), $uid && $o{what} =~ /vnlist/ ? (qw|vnlist.userlist_all vnlist.userlist_obtained|) : (), # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000) $tag_ids ? diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index b3ec9dc6..64cc57d4 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -26,7 +26,6 @@ sub list { { get => 'rfil', required => 0, default => '' }, { get => 'cfil', required => 0, default => '' }, { get => 'vnlist', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref - { get => 'wish', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref ); return $self->resNotFound if $f->{_err}; $f->{q} ||= $f->{sq}; @@ -45,7 +44,6 @@ sub list { }; $f->{vnlist} = $read_write_pref->('vnlist', 'vn_list_own'); - $f->{wish} = $read_write_pref->('wish', 'vn_list_wish'); return $self->resRedirect('/'.$1.$2.(!$3 ? '' : $1 eq 'd' ? '#'.$3 : '.'.$3), 'temp') if $f->{q} && $f->{q} =~ /^([gvrptudcis])([0-9]+)(?:\.([0-9]+))?$/; @@ -64,9 +62,7 @@ sub list { %compat, tagspoil => $self->authPref('spoilers')||0, }, { - what => ' rating' . - ($f->{vnlist} ? ' vnlist' : ''). - ($f->{wish} ? ' wishlist' : ''), + what => ' rating'.($f->{vnlist} ? ' vnlist' : ''), $char ne 'all' ? ( char => $char ) : (), $f->{q} ? ( search => $f->{q} ) : (), keys %$rfil ? ( release => $rfil ) : (), @@ -103,7 +99,6 @@ sub list { if($uid) { p class => 'browseopts'; a href => $url->($char, 'vnlist'), $f->{vnlist} ? (class => 'optselected') : (), 'User VN list'; - a href => $url->($char, 'wish' ), $f->{wish} ? (class => 'optselected') : (), 'Wishlist'; end 'p'; } diff --git a/util/sql/schema.sql b/util/sql/schema.sql index d79277f0..8a1fc8c2 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -764,7 +764,7 @@ CREATE TABLE users ( notify_dbedit boolean NOT NULL DEFAULT TRUE, notify_announce boolean NOT NULL DEFAULT FALSE, vn_list_own boolean NOT NULL DEFAULT FALSE, - vn_list_wish boolean NOT NULL DEFAULT FALSE, + vn_list_wish boolean NOT NULL DEFAULT FALSE, -- Not used anymore, wishlist column in the VN list view has been removed tags_all boolean NOT NULL DEFAULT FALSE, tags_cont boolean NOT NULL DEFAULT TRUE, tags_ero boolean NOT NULL DEFAULT FALSE, |