summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-22 16:51:50 +0100
committerYorhel <git@yorhel.nl>2019-12-22 16:54:42 +0100
commit0916ec2e23f7c15a484781894ee0edee728ecd13 (patch)
tree78ba8b0ea4f4ecceaea6c5e5562d89079b50800e
parentcd5e4dffdf4d99cac7d47433981cfa6d669b2b45 (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.pm15
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm7
-rw-r--r--util/sql/schema.sql2
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,