return strlen($description) > $length ? substr($description, 0, $length-3) . '...' : $description;
}
+ /**
+ * Return a generalised, common raw query that can be 'unioned' across entities.
+ * @return string
+ */
+ public function entityRawQuery()
+ {
+ return "'BookStack\\\\Book' as entity_type, id, id as entity_id, slug, name, {$this->textField} as text,'' as html, '0' as book_id, '0' as priority, '0' as chapter_id, '0' as draft, created_by, updated_by, updated_at, created_at";
+ }
+
}
return strlen($description) > $length ? substr($description, 0, $length-3) . '...' : $description;
}
+ /**
+ * Return a generalised, common raw query that can be 'unioned' across entities.
+ * @return string
+ */
+ public function entityRawQuery()
+ {
+ return "'BookStack\\\\Chapter' as entity_type, id, id as entity_id, slug, name, {$this->textField} as text, '' as html, book_id, priority, '0' as chapter_id, '0' as draft, created_by, updated_by, updated_at, created_at";
+ }
+
}
--- /dev/null
+<?php
+
+namespace BookStack\Console\Commands;
+
+use BookStack\Services\SearchService;
+use Illuminate\Console\Command;
+
+class RegenerateSearch extends Command
+{
+ /**
+ * The name and signature of the console command.
+ *
+ * @var string
+ */
+ protected $signature = 'bookstack:regenerate-search';
+
+ /**
+ * The console command description.
+ *
+ * @var string
+ */
+ protected $description = 'Command description';
+
+ protected $searchService;
+
+ /**
+ * Create a new command instance.
+ *
+ * @param SearchService $searchService
+ */
+ public function __construct(SearchService $searchService)
+ {
+ parent::__construct();
+ $this->searchService = $searchService;
+ }
+
+ /**
+ * Execute the console command.
+ *
+ * @return mixed
+ */
+ public function handle()
+ {
+ $this->searchService->indexAllEntities();
+ }
+}
-<?php
-
-namespace BookStack\Console;
+<?php namespace BookStack\Console;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
* @var array
*/
protected $commands = [
- \BookStack\Console\Commands\ClearViews::class,
- \BookStack\Console\Commands\ClearActivity::class,
- \BookStack\Console\Commands\ClearRevisions::class,
- \BookStack\Console\Commands\RegeneratePermissions::class,
+ Commands\ClearViews::class,
+ Commands\ClearActivity::class,
+ Commands\ClearRevisions::class,
+ Commands\RegeneratePermissions::class,
+ Commands\RegenerateSearch::class
];
/**
class Entity extends Ownable
{
- protected $fieldsToSearch = ['name', 'description'];
+ protected $textField = 'description';
/**
* Compares this entity to another given entity.
return $this->morphMany(Tag::class, 'entity')->orderBy('order', 'asc');
}
+ /**
+ * Get the related search terms.
+ * @return \Illuminate\Database\Eloquent\Relations\MorphMany
+ */
+ public function searchTerms()
+ {
+ return $this->morphMany(SearchTerm::class, 'entity');
+ }
+
/**
* Get this entities restrictions.
*/
return substr($this->name, 0, $length - 3) . '...';
}
+ /**
+ * Get the body text of this entity.
+ * @return mixed
+ */
+ public function getText()
+ {
+ return $this->{$this->textField};
+ }
+
+ /**
+ * Return a generalised, common raw query that can be 'unioned' across entities.
+ * @return string
+ */
+ public function entityRawQuery(){return '';}
+
/**
* Perform a full-text search on this entity.
- * @param string[] $fieldsToSearch
* @param string[] $terms
* @param string[] array $wheres
+ * TODO - REMOVE
* @return mixed
*/
public function fullTextSearchQuery($terms, $wheres = [])
}
$isFuzzy = count($exactTerms) === 0 && count($fuzzyTerms) > 0;
-
+ $fieldsToSearch = ['name', $this->textField];
// Perform fulltext search if relevant terms exist.
if ($isFuzzy) {
$termString = implode(' ', $fuzzyTerms);
- $fields = implode(',', $this->fieldsToSearch);
+
$search = $search->selectRaw('*, MATCH(name) AGAINST(? IN BOOLEAN MODE) AS title_relevance', [$termString]);
- $search = $search->whereRaw('MATCH(' . $fields . ') AGAINST(? IN BOOLEAN MODE)', [$termString]);
+ $search = $search->whereRaw('MATCH(' . implode(',', $fieldsToSearch ). ') AGAINST(? IN BOOLEAN MODE)', [$termString]);
}
// Ensure at least one exact term matches if in search
if (count($exactTerms) > 0) {
- $search = $search->where(function ($query) use ($exactTerms) {
+ $search = $search->where(function ($query) use ($exactTerms, $fieldsToSearch) {
foreach ($exactTerms as $exactTerm) {
- foreach ($this->fieldsToSearch as $field) {
+ foreach ($fieldsToSearch as $field) {
$query->orWhere($field, 'like', $exactTerm);
}
}
<?php namespace BookStack\Http\Controllers;
use BookStack\Repos\EntityRepo;
+use BookStack\Services\SearchService;
use BookStack\Services\ViewService;
use Illuminate\Http\Request;
{
protected $entityRepo;
protected $viewService;
+ protected $searchService;
/**
* SearchController constructor.
* @param EntityRepo $entityRepo
* @param ViewService $viewService
+ * @param SearchService $searchService
*/
- public function __construct(EntityRepo $entityRepo, ViewService $viewService)
+ public function __construct(EntityRepo $entityRepo, ViewService $viewService, SearchService $searchService)
{
$this->entityRepo = $entityRepo;
$this->viewService = $viewService;
+ $this->searchService = $searchService;
parent::__construct();
}
return redirect()->back();
}
$searchTerm = $request->get('term');
- $paginationAppends = $request->only('term');
- $pages = $this->entityRepo->getBySearch('page', $searchTerm, [], 20, $paginationAppends);
- $books = $this->entityRepo->getBySearch('book', $searchTerm, [], 10, $paginationAppends);
- $chapters = $this->entityRepo->getBySearch('chapter', $searchTerm, [], 10, $paginationAppends);
+// $paginationAppends = $request->only('term'); TODO - Check pagination
$this->setPageTitle(trans('entities.search_for_term', ['term' => $searchTerm]));
+
+ $entities = $this->searchService->searchEntities($searchTerm);
+
return view('search/all', [
- 'pages' => $pages,
- 'books' => $books,
- 'chapters' => $chapters,
+ 'entities' => $entities,
'searchTerm' => $searchTerm
]);
}
protected $simpleAttributes = ['name', 'id', 'slug'];
protected $with = ['book'];
-
- protected $fieldsToSearch = ['name', 'text'];
+ protected $textField = 'text';
/**
* Converts this page into a simplified array.
return mb_convert_encoding($text, 'UTF-8');
}
+ /**
+ * Return a generalised, common raw query that can be 'unioned' across entities.
+ * @param bool $withContent
+ * @return string
+ */
+ public function entityRawQuery($withContent = false)
+ { $htmlQuery = $withContent ? 'html' : "'' as html";
+ return "'BookStack\\\\Page' as entity_type, id, id as entity_id, slug, name, {$this->textField} as text, {$htmlQuery}, book_id, priority, chapter_id, draft, created_by, updated_by, updated_at, created_at";
+ }
+
}
use BookStack\PageRevision;
use BookStack\Services\AttachmentService;
use BookStack\Services\PermissionService;
+use BookStack\Services\SearchService;
use BookStack\Services\ViewService;
use Carbon\Carbon;
use DOMDocument;
*/
protected $tagRepo;
+ /**
+ * @var SearchService
+ */
+ protected $searchService;
+
/**
* Acceptable operators to be used in a query
* @var array
protected $queryOperators = ['<=', '>=', '=', '<', '>', 'like', '!='];
/**
- * EntityService constructor.
+ * EntityRepo constructor.
* @param Book $book
* @param Chapter $chapter
* @param Page $page
* @param ViewService $viewService
* @param PermissionService $permissionService
* @param TagRepo $tagRepo
+ * @param SearchService $searchService
*/
public function __construct(
Book $book, Chapter $chapter, Page $page, PageRevision $pageRevision,
- ViewService $viewService, PermissionService $permissionService, TagRepo $tagRepo
+ ViewService $viewService, PermissionService $permissionService,
+ TagRepo $tagRepo, SearchService $searchService
)
{
$this->book = $book;
$this->viewService = $viewService;
$this->permissionService = $permissionService;
$this->tagRepo = $tagRepo;
+ $this->searchService = $searchService;
}
/**
$entity->updated_by = user()->id;
$isChapter ? $book->chapters()->save($entity) : $entity->save();
$this->permissionService->buildJointPermissionsForEntity($entity);
+ $this->searchService->indexEntity($entity);
return $entity;
}
$entityModel->updated_by = user()->id;
$entityModel->save();
$this->permissionService->buildJointPermissionsForEntity($entityModel);
+ $this->searchService->indexEntity($entityModel);
return $entityModel;
}
$this->savePageRevision($page, $input['summary']);
}
+ $this->searchService->indexEntity($page);
+
return $page;
}
$page->text = strip_tags($page->html);
$page->updated_by = user()->id;
$page->save();
+ $this->searchService->indexEntity($page);
return $page;
}
$book->views()->delete();
$book->permissions()->delete();
$this->permissionService->deleteJointPermissionsForEntity($book);
+ $this->searchService->deleteEntityTerms($book);
$book->delete();
}
$chapter->views()->delete();
$chapter->permissions()->delete();
$this->permissionService->deleteJointPermissionsForEntity($chapter);
+ $this->searchService->deleteEntityTerms($chapter);
$chapter->delete();
}
$page->revisions()->delete();
$page->permissions()->delete();
$this->permissionService->deleteJointPermissionsForEntity($page);
+ $this->searchService->deleteEntityTerms($page);
// Delete Attached Files
$attachmentService = app(AttachmentService::class);
--- /dev/null
+<?php namespace BookStack;
+
+use Illuminate\Database\Eloquent\Model;
+
+class SearchTerm extends Model
+{
+
+ protected $fillable = ['term', 'entity_id', 'entity_type', 'score'];
+ public $timestamps = false;
+
+ /**
+ * Get the entity that this term belongs to
+ * @return \Illuminate\Database\Eloquent\Relations\MorphTo
+ */
+ public function entity()
+ {
+ return $this->morphTo('entity');
+ }
+
+}
* @return \Illuminate\Database\Query\Builder
*/
public function bookChildrenQuery($book_id, $filterDrafts = false, $fetchPageContent = false) {
- $pageContentSelect = $fetchPageContent ? 'html' : "''";
- $pageSelect = $this->db->table('pages')->selectRaw("'BookStack\\\\Page' as entity_type, id, slug, name, text, {$pageContentSelect} as description, book_id, priority, chapter_id, draft")->where('book_id', '=', $book_id)->where(function($query) use ($filterDrafts) {
+ $pageSelect = $this->db->table('pages')->selectRaw($this->page->entityRawQuery($fetchPageContent))->where('book_id', '=', $book_id)->where(function($query) use ($filterDrafts) {
$query->where('draft', '=', 0);
if (!$filterDrafts) {
$query->orWhere(function($query) {
});
}
});
- $chapterSelect = $this->db->table('chapters')->selectRaw("'BookStack\\\\Chapter' as entity_type, id, slug, name, '' as text, description, book_id, priority, 0 as chapter_id, 0 as draft")->where('book_id', '=', $book_id);
+ $chapterSelect = $this->db->table('chapters')->selectRaw($this->chapter->entityRawQuery())->where('book_id', '=', $book_id);
$query = $this->db->query()->select('*')->from($this->db->raw("({$pageSelect->toSql()} UNION {$chapterSelect->toSql()}) AS U"))
->mergeBindings($pageSelect)->mergeBindings($chapterSelect);
}
/**
- * Filter items that have entities set a a polymorphic relation.
+ * Filter items that have entities set as a polymorphic relation.
* @param $query
* @param string $tableName
* @param string $entityIdColumn
--- /dev/null
+<?php namespace BookStack\Services;
+
+use BookStack\Book;
+use BookStack\Chapter;
+use BookStack\Entity;
+use BookStack\Page;
+use BookStack\SearchTerm;
+use Illuminate\Database\Connection;
+use Illuminate\Database\Query\JoinClause;
+
+class SearchService
+{
+
+ protected $searchTerm;
+ protected $book;
+ protected $chapter;
+ protected $page;
+ protected $db;
+
+ /**
+ * SearchService constructor.
+ * @param SearchTerm $searchTerm
+ * @param Book $book
+ * @param Chapter $chapter
+ * @param Page $page
+ * @param Connection $db
+ */
+ public function __construct(SearchTerm $searchTerm, Book $book, Chapter $chapter, Page $page, Connection $db)
+ {
+ $this->searchTerm = $searchTerm;
+ $this->book = $book;
+ $this->chapter = $chapter;
+ $this->page = $page;
+ $this->db = $db;
+ }
+
+ public function searchEntities($searchString, $entityType = 'all')
+ {
+ // TODO - Add Tag Searches
+ // TODO - Add advanced custom column searches
+ // TODO - Add exact match searches ("")
+
+ $termArray = explode(' ', $searchString);
+
+ $subQuery = $this->db->table('search_terms')->select('entity_id', 'entity_type', \DB::raw('SUM(score) as score'));
+ $subQuery->where(function($query) use ($termArray) {
+ foreach ($termArray as $inputTerm) {
+ $query->orWhere('term', 'like', $inputTerm .'%');
+ }
+ });
+
+ $subQuery = $subQuery->groupBy('entity_type', 'entity_id');
+ $pageSelect = $this->db->table('pages as e')->join(\DB::raw('(' . $subQuery->toSql() . ') as s'), function(JoinClause $join) {
+ $join->on('e.id', '=', 's.entity_id');
+ })->selectRaw('e.*, s.score')->orderBy('score', 'desc');
+ $pageSelect->mergeBindings($subQuery);
+ dd($pageSelect->toSql());
+ // TODO - Continue from here
+ }
+
+ /**
+ * Index the given entity.
+ * @param Entity $entity
+ */
+ public function indexEntity(Entity $entity)
+ {
+ $this->deleteEntityTerms($entity);
+ $nameTerms = $this->generateTermArrayFromText($entity->name, 5);
+ $bodyTerms = $this->generateTermArrayFromText($entity->getText(), 1);
+ $terms = array_merge($nameTerms, $bodyTerms);
+ $entity->searchTerms()->createMany($terms);
+ }
+
+ /**
+ * Index multiple Entities at once
+ * @param Entity[] $entities
+ */
+ protected function indexEntities($entities) {
+ $terms = [];
+ foreach ($entities as $entity) {
+ $nameTerms = $this->generateTermArrayFromText($entity->name, 5);
+ $bodyTerms = $this->generateTermArrayFromText($entity->getText(), 1);
+ foreach (array_merge($nameTerms, $bodyTerms) as $term) {
+ $term['entity_id'] = $entity->id;
+ $term['entity_type'] = $entity->getMorphClass();
+ $terms[] = $term;
+ }
+ }
+ $this->searchTerm->insert($terms);
+ }
+
+ /**
+ * Delete and re-index the terms for all entities in the system.
+ */
+ public function indexAllEntities()
+ {
+ $this->searchTerm->truncate();
+
+ // Chunk through all books
+ $this->book->chunk(500, function ($books) {
+ $this->indexEntities($books);
+ });
+
+ // Chunk through all chapters
+ $this->chapter->chunk(500, function ($chapters) {
+ $this->indexEntities($chapters);
+ });
+
+ // Chunk through all pages
+ $this->page->chunk(500, function ($pages) {
+ $this->indexEntities($pages);
+ });
+ }
+
+ /**
+ * Delete related Entity search terms.
+ * @param Entity $entity
+ */
+ public function deleteEntityTerms(Entity $entity)
+ {
+ $entity->searchTerms()->delete();
+ }
+
+ /**
+ * Create a scored term array from the given text.
+ * @param $text
+ * @param float|int $scoreAdjustment
+ * @return array
+ */
+ protected function generateTermArrayFromText($text, $scoreAdjustment = 1)
+ {
+ $tokenMap = []; // {TextToken => OccurrenceCount}
+ $splitText = explode(' ', $text);
+ foreach ($splitText as $token) {
+ if ($token === '') continue;
+ if (!isset($tokenMap[$token])) $tokenMap[$token] = 0;
+ $tokenMap[$token]++;
+ }
+
+ $terms = [];
+ foreach ($tokenMap as $token => $count) {
+ $terms[] = [
+ 'term' => $token,
+ 'score' => $count * $scoreAdjustment
+ ];
+ }
+ return $terms;
+ }
+
+}
\ No newline at end of file
--- /dev/null
+<?php
+
+use Illuminate\Support\Facades\Schema;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Database\Migrations\Migration;
+
+class CreateSearchIndexTable extends Migration
+{
+ /**
+ * Run the migrations.
+ *
+ * @return void
+ */
+ public function up()
+ {
+ Schema::create('search_terms', function (Blueprint $table) {
+ $table->increments('id');
+ $table->string('term', 200);
+ $table->string('entity_type', 100);
+ $table->integer('entity_id');
+ $table->integer('score');
+
+ $table->index('term');
+ $table->index('entity_type');
+ $table->index(['entity_type', 'entity_id']);
+ $table->index('score');
+ });
+
+ // TODO - Drop old fulltext indexes
+
+ app(\BookStack\Services\SearchService::class)->indexAllEntities();
+ }
+
+ /**
+ * Reverse the migrations.
+ *
+ * @return void
+ */
+ public function down()
+ {
+ Schema::dropIfExists('search_terms');
+ }
+}
<h1>{{ trans('entities.search_results') }}</h1>
<p>
- @if(count($pages) > 0)
- <a href="{{ baseUrl("/search/pages?term={$searchTerm}") }}" class="text-page"><i class="zmdi zmdi-file-text"></i>{{ trans('entities.search_view_pages') }}</a>
- @endif
-
- @if(count($chapters) > 0)
-
- <a href="{{ baseUrl("/search/chapters?term={$searchTerm}") }}" class="text-chapter"><i class="zmdi zmdi-collection-bookmark"></i>{{ trans('entities.search_view_chapters') }}</a>
- @endif
-
- @if(count($books) > 0)
-
- <a href="{{ baseUrl("/search/books?term={$searchTerm}") }}" class="text-book"><i class="zmdi zmdi-book"></i>{{ trans('entities.search_view_books') }}</a>
- @endif
+ {{--TODO - Remove these pages--}}
+ Remove these links (Commented out)
+ {{--@if(count($pages) > 0)--}}
+ {{--<a href="{{ baseUrl("/search/pages?term={$searchTerm}") }}" class="text-page"><i class="zmdi zmdi-file-text"></i>{{ trans('entities.search_view_pages') }}</a>--}}
+ {{--@endif--}}
+
+ {{--@if(count($chapters) > 0)--}}
+ {{-- --}}
+ {{--<a href="{{ baseUrl("/search/chapters?term={$searchTerm}") }}" class="text-chapter"><i class="zmdi zmdi-collection-bookmark"></i>{{ trans('entities.search_view_chapters') }}</a>--}}
+ {{--@endif--}}
+
+ {{--@if(count($books) > 0)--}}
+ {{-- --}}
+ {{--<a href="{{ baseUrl("/search/books?term={$searchTerm}") }}" class="text-book"><i class="zmdi zmdi-book"></i>{{ trans('entities.search_view_books') }}</a>--}}
+ {{--@endif--}}
</p>
<div class="row">
<div class="col-md-6">
<h3><a href="{{ baseUrl("/search/pages?term={$searchTerm}") }}" class="no-color">{{ trans('entities.pages') }}</a></h3>
- @include('partials/entity-list', ['entities' => $pages, 'style' => 'detailed'])
+ @include('partials/entity-list', ['entities' => $entities, 'style' => 'detailed'])
</div>
<div class="col-md-5 col-md-offset-1">
- @if(count($books) > 0)
- <h3><a href="{{ baseUrl("/search/books?term={$searchTerm}") }}" class="no-color">{{ trans('entities.books') }}</a></h3>
- @include('partials/entity-list', ['entities' => $books])
- @endif
-
- @if(count($chapters) > 0)
- <h3><a href="{{ baseUrl("/search/chapters?term={$searchTerm}") }}" class="no-color">{{ trans('entities.chapters') }}</a></h3>
- @include('partials/entity-list', ['entities' => $chapters])
- @endif
+ Sidebar filter controls
</div>
</div>