-
Notifications
You must be signed in to change notification settings - Fork 2
/
FulltextGambit.php
80 lines (71 loc) · 3.55 KB
/
FulltextGambit.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?php
/*
* Modified from Flarum\Discussion\Search\Gambit\FulltextGambit.
*
* (c) Toby Zerner <toby.zerner@gmail.com>
*
* Copyright (c) 2018-2021 Yixuan Qiu
*
*/
namespace Cosname\Search;
use Flarum\Post\Post;
use Flarum\Search\GambitInterface;
use Flarum\Search\SearchState;
use Illuminate\Database\Query\Expression;
class FulltextGambit implements GambitInterface
{
/**
* {@inheritdoc}
*/
public function apply(SearchState $search, $bit)
{
// Replace all non-word characters with spaces.
// We do this to prevent MySQL fulltext search boolean mode from taking
// effect: https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html
$bit = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $bit);
$query = $search->getQuery();
$grammar = $query->getGrammar();
// Construct a subquery to fetch discussions which contain relevant
// posts. Retrieve the collective relevance of each discussion's posts,
// which we will use later in the order by clause, and also retrieve
// the ID of the most relevant post.
// If the string to be searched for has at least two characters
// and contains non-ASCII characters,
// use the slow but exact match, https://discuss.flarum.org.cn/d/321
if ((mb_strlen($bit) >= 2) && preg_match('/[^\x20-\x7f]/', $bit)) {
$subquery = Post::whereVisibleTo($search->getActor())
->select('posts.discussion_id')
->selectRaw('count(*) as score')
->selectRaw('SUBSTRING_INDEX(GROUP_CONCAT('.$grammar->wrap('posts.id').' ORDER BY '.$grammar->wrap('posts.number').'), \',\', 1) as most_relevant_post_id')
->where('posts.type', 'comment')
->where('posts.content', 'like', '%' . $bit . '%')
->groupBy('posts.discussion_id');
} else {
// Otherwise, use the fast full-text search
$subquery = Post::whereVisibleTo($search->getActor())
->select('posts.discussion_id')
->selectRaw('SUM(MATCH('.$grammar->wrap('posts.content').') AGAINST (?)) as score', [$bit])
->selectRaw('SUBSTRING_INDEX(GROUP_CONCAT('.$grammar->wrap('posts.id').' ORDER BY MATCH('.$grammar->wrap('posts.content').') AGAINST (?) DESC, '.$grammar->wrap('posts.number').'), \',\', 1) as most_relevant_post_id', [$bit])
->where('posts.type', 'comment')
->whereRaw('MATCH('.$grammar->wrap('posts.content').') AGAINST (? IN BOOLEAN MODE)', [$bit])
->groupBy('posts.discussion_id');
}
// Join the subquery into the main search query and scope results to
// discussions that have a relevant title or that contain relevant posts.
$query
->addSelect('posts_ft.most_relevant_post_id')
->join(
new Expression('('.$subquery->toSql().') '.$grammar->wrapTable('posts_ft')),
'posts_ft.discussion_id', '=', 'discussions.id'
)
->addBinding($subquery->getBindings(), 'join')
->where(function ($query) use ($grammar, $bit) {
$query->whereRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (? IN BOOLEAN MODE)', [$bit])
->orWhereNotNull('posts_ft.score');
});
$search->setDefaultSort(function ($query) use ($grammar, $bit) {
$query->orderByRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (?) desc', [$bit]);
$query->orderBy('posts_ft.score', 'desc');
});
}
}