-
-
Notifications
You must be signed in to change notification settings - Fork 9
/
FulltextGambit.php
69 lines (60 loc) · 2.88 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
<?php
/*
* This file is part of Flarum.
*
* For detailed copyright and license information, please view the
* LICENSE file that was distributed with this source code.
*/
namespace Flarum\Discussion\Search\Gambit;
use Flarum\Discussion\Discussion;
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}\p{M}_]+/u', ' ', $bit);
$query = $search->getQuery();
$grammar = $query->getGrammar();
$discussionSubquery = Discussion::select('id')
->selectRaw('NULL as score')
->selectRaw('first_post_id as most_relevant_post_id')
->whereRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (? IN BOOLEAN MODE)', [$bit]);
// 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.
$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')
->union($discussionSubquery);
// 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'
)
->groupBy('discussions.id')
->addBinding($subquery->getBindings(), 'join');
$search->setDefaultSort(function ($query) use ($grammar, $bit) {
$query->orderByRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (?) desc', [$bit]);
$query->orderBy('posts_ft.score', 'desc');
});
}
}