diff options
author | Yorhel <git@yorhel.nl> | 2015-09-07 01:35:48 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-09-07 01:35:48 +0200 |
commit | 438d4df64d950f8905bd31bddc203d408f42f125 (patch) | |
tree | d2ebd6f9ea86238773cfdc35ac41033dcb9baffa /lib/VNDB/DB | |
parent | 19ce5fcf536ed478ad34b6b1014bf6f44841d25d (diff) |
Implement discussion board search function
Inspired by wakaranai's implementation at
https://github.com/morkt/vndb/commit/b852c87ad145fdaaa09c79b6378dd819b46f7e87
This version is different in a number of aspects:
- Separate search functions for title search and fulltext post search.
Perhaps not the most convenient option, but the downside of a combined
search is that if the query matches the threads' title, then all of
the posts in that thread will show up in the results. This didn't seem
very useful.
- Sorting is based purely on post date. Rank-based sort is slow without
a separate caching column, and in my opinion not all that useful.
Implementation differences:
- Integrated in the existing DB::Discussions functions, so less code to
maintain and more code reuse.
- No separate caching column for the tsvector, a functional index is
used instead. This is a bit slower (index results need to be
re-checked against the actual messages, hence the slowdown), but has
the advantage of smaller database dumps and less complexity in
updating the cache.
Things to fix or look at:
- Highlighting of the search query in message contents.
- Allow or-style query matching
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 21 |
1 files changed, 16 insertions, 5 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index 574560a0..723170e2 100644 --- a/lib/VNDB/DB/Discussions.pm +++ b/lib/VNDB/DB/Discussions.pm @@ -8,7 +8,7 @@ use Exporter 'import'; our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount|; -# Options: id, type, iid, results, page, what, notusers, sort, reverse +# Options: id, type, iid, results, page, what, notusers, search, sort, reverse # What: boards, boardtitles, firstpost, lastpost # Sort: id lastpost sub dbThreadGet { @@ -17,19 +17,26 @@ sub dbThreadGet { $o{page} ||= 1; $o{what} ||= ''; - my %where = ( + my @where = ( $o{id} ? ( 't.id = ?' => $o{id} ) : (), !$o{id} ? ( 't.hidden = FALSE' => 0 ) : (), $o{type} && !$o{iid} ? ( - 't.id IN(SELECT tid FROM threads_boards WHERE type = ?)' => $o{type} ) : (), + 't.id IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), $o{type} && $o{iid} ? ( 'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (), $o{notusers} ? ( 't.id NOT IN(SELECT tid FROM threads_boards WHERE type = \'u\')' => 1) : (), ); + if($o{search}) { + for (split /[ -,._]/, $o{search}) { + s/%//g; + push @where, 't.title ilike ?', "%$_%" if length($_) > 0; + } + } + my @select = ( qw|t.id t.title t.count t.locked t.hidden|, $o{what} =~ /firstpost/ ? ('tpf.uid AS fuid', q|EXTRACT('epoch' from tpf.date) AS fdate|, 'uf.username AS fusername') : (), @@ -60,7 +67,7 @@ sub dbThreadGet { !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $order + join(', ', @select), join(' ', @join), \@where, $order ); if($o{what} =~ /(boards|boardtitles)/ && $#$r >= 0) { @@ -158,7 +165,7 @@ sub dbThreadCount { } -# Options: tid, num, what, uid, mindate, hide, page, results, sort, reverse +# Options: tid, num, what, uid, mindate, hide, search, type, page, results, sort, reverse # what: user thread sub dbPostGet { my($self, %o) = @_; @@ -179,6 +186,10 @@ sub dbPostGet { 'tp.hidden = FALSE' => 1 ) : (), $o{hide} && $o{what} =~ /thread/ ? ( 't.hidden = FALSE' => 1 ) : (), + $o{search} ? ( + q{to_tsvector('english', strip_bb_tags(msg)) @@ to_tsquery(?)} => $o{search}) : (), + $o{type} ? ( + 'tp.tid = ANY(ARRAY(SELECT tid FROM threads_boards WHERE type IN(!l)))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), ); my @select = ( |