3 namespace BookStack\Entities\Tools;
5 use BookStack\Auth\Permissions\PermissionService;
6 use BookStack\Auth\User;
7 use BookStack\Entities\EntityProvider;
8 use BookStack\Entities\Models\Entity;
9 use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
10 use Illuminate\Database\Eloquent\Collection as EloquentCollection;
11 use Illuminate\Database\Query\Builder;
12 use Illuminate\Database\Query\JoinClause;
13 use Illuminate\Support\Collection;
14 use Illuminate\Support\Facades\DB;
15 use Illuminate\Support\Str;
22 protected $entityProvider;
25 * @var PermissionService
27 protected $permissionService;
30 * Acceptable operators to be used in a query.
34 protected $queryOperators = ['<=', '>=', '=', '<', '>', 'like', '!='];
36 public function __construct(EntityProvider $entityProvider, PermissionService $permissionService)
38 $this->entityProvider = $entityProvider;
39 $this->permissionService = $permissionService;
43 * Search all entities in the system.
44 * The provided count is for each entity to search,
45 * Total returned could be larger and not guaranteed.
47 public function searchEntities(SearchOptions $searchOpts, string $entityType = 'all', int $page = 1, int $count = 20, string $action = 'view'): array
49 $entityTypes = array_keys($this->entityProvider->all());
50 $entityTypesToSearch = $entityTypes;
52 if ($entityType !== 'all') {
53 $entityTypesToSearch = $entityType;
54 } elseif (isset($searchOpts->filters['type'])) {
55 $entityTypesToSearch = explode('|', $searchOpts->filters['type']);
62 foreach ($entityTypesToSearch as $entityType) {
63 if (!in_array($entityType, $entityTypes)) {
67 $searchQuery = $this->buildQuery($searchOpts, $entityType, $action);
68 $entityTotal = $searchQuery->count();
69 $searchResults = $this->getPageOfDataFromQuery($searchQuery, $page, $count);
71 if ($entityTotal > ($page * $count)) {
75 $total += $entityTotal;
76 $results = $results->merge($searchResults);
81 'count' => count($results),
82 'has_more' => $hasMore,
83 'results' => $results->sortByDesc('score')->values(),
88 * Search a book for entities.
90 public function searchBook(int $bookId, string $searchString): Collection
92 $opts = SearchOptions::fromString($searchString);
93 $entityTypes = ['page', 'chapter'];
94 $entityTypesToSearch = isset($opts->filters['type']) ? explode('|', $opts->filters['type']) : $entityTypes;
97 foreach ($entityTypesToSearch as $entityType) {
98 if (!in_array($entityType, $entityTypes)) {
101 $search = $this->buildQuery($opts, $entityType)->where('book_id', '=', $bookId)->take(20)->get();
102 $results = $results->merge($search);
105 return $results->sortByDesc('score')->take(20);
109 * Search a chapter for entities.
111 public function searchChapter(int $chapterId, string $searchString): Collection
113 $opts = SearchOptions::fromString($searchString);
114 $pages = $this->buildQuery($opts, 'page')->where('chapter_id', '=', $chapterId)->take(20)->get();
116 return $pages->sortByDesc('score');
120 * Get a page of result data from the given query based on the provided page parameters.
122 protected function getPageOfDataFromQuery(EloquentBuilder $query, int $page = 1, int $count = 20): EloquentCollection
124 return $query->clone()
125 ->skip(($page - 1) * $count)
131 * Create a search query for an entity.
133 protected function buildQuery(SearchOptions $searchOpts, string $entityType = 'page', string $action = 'view'): EloquentBuilder
135 $entity = $this->entityProvider->get($entityType);
136 $entitySelect = $entity->newQuery();
138 // Handle normal search terms
139 if (count($searchOpts->searches) > 0) {
140 $rawScoreSum = DB::raw('SUM(score) as score');
141 $subQuery = DB::table('search_terms')->select('entity_id', 'entity_type', $rawScoreSum);
142 $subQuery->where('entity_type', '=', $entity->getMorphClass());
143 $subQuery->where(function (Builder $query) use ($searchOpts) {
144 foreach ($searchOpts->searches as $inputTerm) {
145 $query->orWhere('term', 'like', $inputTerm . '%');
147 })->groupBy('entity_type', 'entity_id');
148 $entitySelect->join(DB::raw('(' . $subQuery->toSql() . ') as s'), function (JoinClause $join) {
149 $join->on('id', '=', 'entity_id');
150 })->addSelect($entity->getTable() . '.*')
151 ->selectRaw('s.score')
152 ->orderBy('score', 'desc');
153 $entitySelect->mergeBindings($subQuery);
156 // Handle exact term matching
157 foreach ($searchOpts->exacts as $inputTerm) {
158 $entitySelect->where(function (EloquentBuilder $query) use ($inputTerm, $entity) {
159 $query->where('name', 'like', '%' . $inputTerm . '%')
160 ->orWhere($entity->textField, 'like', '%' . $inputTerm . '%');
164 // Handle tag searches
165 foreach ($searchOpts->tags as $inputTerm) {
166 $this->applyTagSearch($entitySelect, $inputTerm);
170 foreach ($searchOpts->filters as $filterTerm => $filterValue) {
171 $functionName = Str::camel('filter_' . $filterTerm);
172 if (method_exists($this, $functionName)) {
173 $this->$functionName($entitySelect, $entity, $filterValue);
177 return $this->permissionService->enforceEntityRestrictions($entity, $entitySelect, $action);
181 * Get the available query operators as a regex escaped list.
183 protected function getRegexEscapedOperators(): string
185 $escapedOperators = [];
186 foreach ($this->queryOperators as $operator) {
187 $escapedOperators[] = preg_quote($operator);
190 return implode('|', $escapedOperators);
194 * Apply a tag search term onto a entity query.
196 protected function applyTagSearch(EloquentBuilder $query, string $tagTerm): EloquentBuilder
198 preg_match('/^(.*?)((' . $this->getRegexEscapedOperators() . ')(.*?))?$/', $tagTerm, $tagSplit);
199 $query->whereHas('tags', function (EloquentBuilder $query) use ($tagSplit) {
200 $tagName = $tagSplit[1];
201 $tagOperator = count($tagSplit) > 2 ? $tagSplit[3] : '';
202 $tagValue = count($tagSplit) > 3 ? $tagSplit[4] : '';
203 $validOperator = in_array($tagOperator, $this->queryOperators);
204 if (!empty($tagOperator) && !empty($tagValue) && $validOperator) {
205 if (!empty($tagName)) {
206 $query->where('name', '=', $tagName);
208 if (is_numeric($tagValue) && $tagOperator !== 'like') {
209 // We have to do a raw sql query for this since otherwise PDO will quote the value and MySQL will
210 // search the value as a string which prevents being able to do number-based operations
211 // on the tag values. We ensure it has a numeric value and then cast it just to be sure.
212 $tagValue = (float) trim($query->getConnection()->getPdo()->quote($tagValue), "'");
213 $query->whereRaw("value ${tagOperator} ${tagValue}");
215 $query->where('value', $tagOperator, $tagValue);
218 $query->where('name', '=', $tagName);
226 * Custom entity search filters.
228 protected function filterUpdatedAfter(EloquentBuilder $query, Entity $model, $input): void
231 $date = date_create($input);
232 $query->where('updated_at', '>=', $date);
233 } catch (\Exception $e) {}
236 protected function filterUpdatedBefore(EloquentBuilder $query, Entity $model, $input): void
239 $date = date_create($input);
240 $query->where('updated_at', '<', $date);
241 } catch (\Exception $e) {}
244 protected function filterCreatedAfter(EloquentBuilder $query, Entity $model, $input): void
247 $date = date_create($input);
248 $query->where('created_at', '>=', $date);
249 } catch (\Exception $e) {}
252 protected function filterCreatedBefore(EloquentBuilder $query, Entity $model, $input)
255 $date = date_create($input);
256 $query->where('created_at', '<', $date);
257 } catch (\Exception $e) {}
260 protected function filterCreatedBy(EloquentBuilder $query, Entity $model, $input)
262 $userSlug = $input === 'me' ? user()->slug : trim($input);
263 $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
265 $query->where('created_by', '=', $user->id);
269 protected function filterUpdatedBy(EloquentBuilder $query, Entity $model, $input)
271 $userSlug = $input === 'me' ? user()->slug : trim($input);
272 $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
274 $query->where('updated_by', '=', $user->id);
278 protected function filterOwnedBy(EloquentBuilder $query, Entity $model, $input)
280 $userSlug = $input === 'me' ? user()->slug : trim($input);
281 $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
283 $query->where('owned_by', '=', $user->id);
287 protected function filterInName(EloquentBuilder $query, Entity $model, $input)
289 $query->where('name', 'like', '%' . $input . '%');
292 protected function filterInTitle(EloquentBuilder $query, Entity $model, $input)
294 $this->filterInName($query, $model, $input);
297 protected function filterInBody(EloquentBuilder $query, Entity $model, $input)
299 $query->where($model->textField, 'like', '%' . $input . '%');
302 protected function filterIsRestricted(EloquentBuilder $query, Entity $model, $input)
304 $query->where('restricted', '=', true);
307 protected function filterViewedByMe(EloquentBuilder $query, Entity $model, $input)
309 $query->whereHas('views', function ($query) {
310 $query->where('user_id', '=', user()->id);
314 protected function filterNotViewedByMe(EloquentBuilder $query, Entity $model, $input)
316 $query->whereDoesntHave('views', function ($query) {
317 $query->where('user_id', '=', user()->id);
321 protected function filterSortBy(EloquentBuilder $query, Entity $model, $input)
323 $functionName = Str::camel('sort_by_' . $input);
324 if (method_exists($this, $functionName)) {
325 $this->$functionName($query, $model);
330 * Sorting filter options.
332 protected function sortByLastCommented(EloquentBuilder $query, Entity $model)
334 $commentsTable = DB::getTablePrefix() . 'comments';
335 $morphClass = str_replace('\\', '\\\\', $model->getMorphClass());
336 $commentQuery = DB::raw('(SELECT c1.entity_id, c1.entity_type, c1.created_at as last_commented FROM ' . $commentsTable . ' c1 LEFT JOIN ' . $commentsTable . ' c2 ON (c1.entity_id = c2.entity_id AND c1.entity_type = c2.entity_type AND c1.created_at < c2.created_at) WHERE c1.entity_type = \'' . $morphClass . '\' AND c2.created_at IS NULL) as comments');
338 $query->join($commentQuery, $model->getTable() . '.id', '=', 'comments.entity_id')->orderBy('last_commented', 'desc');