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. :(

1 files changed, 6 insertions, 9 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 ?