diff options
author | Yorhel <git@yorhel.nl> | 2019-11-23 11:02:18 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-11-23 11:02:20 +0100 |
commit | a185a7bcb459b4961e375e43fd19d3133a66aed2 (patch) | |
tree | f8c4b9cddc15394e46190a7b955f36201bd56358 /lib | |
parent | ddb7e51f4c4090a096e5eaa79b8e91b4d1040a31 (diff) |
ulist: Make labels column sortable
This is pretty slow for large lists, but let's just see how it goes.
Diffstat (limited to 'lib')
-rw-r--r-- | lib/VNWeb/User/Lists.pm | 35 |
1 files changed, 18 insertions, 17 deletions
diff --git a/lib/VNWeb/User/Lists.pm b/lib/VNWeb/User/Lists.pm index b988d6cf..b2adba7b 100644 --- a/lib/VNWeb/User/Lists.pm +++ b/lib/VNWeb/User/Lists.pm @@ -253,7 +253,7 @@ sub filters_ { my $opt = eval { tuwf->validate(get => p => { upage => 1 }, l => { type => 'array', scalar => 1, required => 0, default => [], values => { int => 1 } }, - s => { required => 0, default => 'title', enum => [qw[ title vote added started finished ]] }, + s => { required => 0, default => 'title', enum => [qw[ title vote added label started finished ]] }, o => { required => 0, default => 'a', enum => ['a', 'd'] }, )->data } || { p => 1, l => [], s => 'title', o => 'a' }; @@ -366,31 +366,32 @@ sub listing_ { my($unlabeled) = grep $_ == -1, $opt->{l}->@*; my @where_vns = ( - @l ? sql('ul.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@l, ')') : - !$own ? sql('ul.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))') : (), - $unlabeled ? sql('NOT EXISTS(SELECT 1 FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid = ul.vid AND lbl <> ', \7, ')') : () + @l ? sql('uv.vid IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@l, ')') : + !$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))') : (), + $unlabeled ? sql('NOT EXISTS(SELECT 1 FROM ulist_vns_labels WHERE uid =', \$uid, 'AND vid = uv.vid AND lbl <> ', \7, ')') : () ); my $where = sql_and - sql('ul.uid =', \$uid), + sql('uv.uid =', \$uid), @where_vns ? sql_or(@where_vns) : (); - my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns ul WHERE', $where); + my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv WHERE', $where); my $lst = tuwf->dbPagei({ page => $opt->{p}, results => 50 }, - 'SELECT v.id, v.title, v.original, ul.vote, ul.notes, ul.started, ul.finished - ,', sql_totime('ul.added'), ' as added - ,', sql_totime('ul.lastmod'), ' as lastmod - ,', sql_totime('ul.vote_date'), ' as vote_date - FROM ulist_vns ul - JOIN vn v ON v.id = ul.vid + 'SELECT v.id, v.title, v.original, uv.vote, uv.notes, uv.started, uv.finished + ,', sql_totime('uv.added'), ' as added + ,', sql_totime('uv.lastmod'), ' as lastmod + ,', sql_totime('uv.vote_date'), ' as vote_date + FROM ulist_vns uv + JOIN vn v ON v.id = uv.vid WHERE', $where, ' ORDER BY', { title => 'v.title', - vote => 'ul.vote', - added => 'ul.added', - started => 'ul.started', - finished => 'ul.finished' + vote => 'uv.vote', + added => 'uv.added', + started => 'uv.started', + finished => 'uv.finished', + label => sql('ARRAY(SELECT ul.label FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl <> ', \7, ')') }->{$opt->{s}}, $opt->{o} eq 'd' ? 'DESC' : 'ASC', 'NULLS LAST, v.title' ); @@ -421,7 +422,7 @@ sub listing_ { label_ for => 'collapse_vid', sub { txt_ 'Opt' }; }; td_ class => 'tc2', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url; debug_ $lst }; - td_ class => 'tc3', 'Labels'; + td_ class => 'tc3', sub { txt_ 'Labels'; sortable_ 'label', $opt, \&url }; td_ class => 'tc4', sub { txt_ 'Vote'; sortable_ 'vote', $opt, \&url }; td_ class => 'tc5', sub { txt_ 'Added'; sortable_ 'added', $opt, \&url }; td_ class => 'tc6', sub { txt_ 'Start date'; sortable_ 'started', $opt, \&url }; |