Search: Started work to make search result size consistent

diff --git a/app/Entities/Models/Entity.php b/app/Entities/Models/Entity.php
index 77393cbbc4e8fd347ba8337c482729b03aa62a7c..b47a029ece82e4268dff7f0345364d7bf6256eb8 100644 (file)
--- a/app/Entities/Models/Entity.php
+++ b/app/Entities/Models/Entity.php
@@ -471,4 +471,17 @@ abstract class Entity extends Model implements
 
         return $contentFields;
     }
+
+    /**
+     * Create a new instance for the given entity type.
+     */
+    public static function instanceFromType(string $type): self
+    {
+        return match ($type) {
+            'page' => new Page(),
+            'chapter' => new Chapter(),
+            'book' => new Book(),
+            'bookshelf' => new Bookshelf(),
+        };
+    }
 }

diff --git a/app/Entities/Models/EntityTable.php b/app/Entities/Models/EntityTable.php
new file mode 100644 (file)
index 0000000..50112a8
--- /dev/null
+++ b/app/Entities/Models/EntityTable.php
@@ -0,0 +1,37 @@
+<?php
+
+namespace BookStack\Entities\Models;
+
+use BookStack\App\Model;
+use BookStack\Permissions\Models\JointPermission;
+use BookStack\Permissions\PermissionApplicator;
+use Illuminate\Database\Eloquent\Builder;
+use Illuminate\Database\Eloquent\Relations\HasMany;
+use Illuminate\Database\Eloquent\SoftDeletes;
+
+/**
+ * This is a simplistic model interpretation of a generic Entity used to query and represent
+ * that database abstractly. Generally, this should rarely be used outside queries.
+ */
+class EntityTable extends Model
+{
+    use SoftDeletes;
+
+    protected $table = 'entities';
+
+    /**
+     * Get the entities that are visible to the current user.
+     */
+    public function scopeVisible(Builder $query): Builder
+    {
+        return app()->make(PermissionApplicator::class)->restrictEntityQuery($query);
+    }
+
+    /**
+     * Get the entity jointPermissions this is connected to.
+     */
+    public function jointPermissions(): HasMany
+    {
+        return $this->hasMany(JointPermission::class, 'entity_id')->whereColumn('entity_type', '=', 'entities.type');
+    }
+}

diff --git a/app/Entities/Queries/EntityQueries.php b/app/Entities/Queries/EntityQueries.php
index a7a037916d50451fbdbf825f479d02fec5d55658..c27cc61ccb1a34b7c0e1ca3b32ed4c2ca0491a80 100644 (file)
--- a/app/Entities/Queries/EntityQueries.php
+++ b/app/Entities/Queries/EntityQueries.php
@@ -3,7 +3,10 @@
 namespace BookStack\Entities\Queries;
 
 use BookStack\Entities\Models\Entity;
+use BookStack\Entities\Models\EntityTable;
 use Illuminate\Database\Eloquent\Builder;
+use Illuminate\Database\Query\JoinClause;
+use Illuminate\Support\Facades\DB;
 use InvalidArgumentException;
 
 class EntityQueries
@@ -32,12 +35,31 @@ class EntityQueries
         return $queries->findVisibleById($entityId);
     }
 
+    /**
+     * Start a query across all entity types.
+     * Combines the description/text fields into a single 'description' field.
+     * @return Builder<EntityTable>
+     */
+    public function visibleForList(): Builder
+    {
+        $rawDescriptionField = DB::raw('COALESCE(description, text) as description');
+        return EntityTable::query()->scopes('visible')
+            ->select(['id', 'type', 'name', 'slug', 'book_id', 'chapter_id', 'created_at', 'updated_at', 'draft', $rawDescriptionField])
+            ->leftJoin('entity_container_data', function (JoinClause $join) {
+                $join->on('entity_container_data.entity_id', '=', 'entities.id')
+                    ->on('entity_container_data.entity_type', '=', 'entities.type');
+            })->leftJoin('entity_page_data', function (JoinClause $join) {
+                $join->on('entity_page_data.page_id', '=', 'entities.id')
+                    ->where('entities.type', '=', 'page');
+            });
+    }
+
     /**
      * Start a query of visible entities of the given type,
      * suitable for listing display.
      * @return Builder<Entity>
      */
-    public function visibleForList(string $entityType): Builder
+    public function visibleForListForType(string $entityType): Builder
     {
         $queries = $this->getQueriesForType($entityType);
         return $queries->visibleForList();
@@ -48,7 +70,7 @@ class EntityQueries
      * suitable for using the contents of the items.
      * @return Builder<Entity>
      */
-    public function visibleForContent(string $entityType): Builder
+    public function visibleForContentForType(string $entityType): Builder
     {
         $queries = $this->getQueriesForType($entityType);
         return $queries->visibleForContent();

diff --git a/app/Entities/Tools/EntityHydrator.php b/app/Entities/Tools/EntityHydrator.php
new file mode 100644 (file)
index 0000000..fbfc876
--- /dev/null
+++ b/app/Entities/Tools/EntityHydrator.php
@@ -0,0 +1,151 @@
+<?php
+
+namespace BookStack\Entities\Tools;
+
+use BookStack\Activity\Models\Tag;
+use BookStack\Entities\Models\Chapter;
+use BookStack\Entities\Models\Entity;
+use BookStack\Entities\Models\EntityTable;
+use BookStack\Entities\Models\Page;
+use BookStack\Entities\Queries\EntityQueries;
+use Illuminate\Database\Eloquent\Collection;
+
+class EntityHydrator
+{
+    /**
+     * @var EntityTable[] $entities
+     */
+    protected array $entities;
+
+    protected bool $loadTags = false;
+    protected bool $loadParents = false;
+
+    public function __construct(array $entities, bool $loadTags = false, bool $loadParents = false)
+    {
+        $this->entities = $entities;
+        $this->loadTags = $loadTags;
+        $this->loadParents = $loadParents;
+    }
+
+    /**
+     * Hydrate the entities of this hydrator to return a list of entities represented
+     * in their original intended models.
+     * @return Entity[]
+     */
+    public function hydrate(): array
+    {
+        $hydrated = [];
+
+        foreach ($this->entities as $entity) {
+            $data = $entity->toArray();
+            $instance = Entity::instanceFromType($entity->type);
+
+            if ($instance instanceof Page) {
+                $data['text'] = $data['description'];
+                unset($data['description']);
+            }
+
+            $instance->forceFill($data);
+            $hydrated[] = $instance;
+        }
+
+        if ($this->loadTags) {
+            $this->loadTagsIntoModels($hydrated);
+        }
+
+        if ($this->loadParents) {
+            $this->loadParentsIntoModels($hydrated);
+        }
+
+        return $hydrated;
+    }
+
+    /**
+     * @param Entity[] $entities
+     */
+    protected function loadTagsIntoModels(array $entities): void
+    {
+        $idsByType = [];
+        $entityMap = [];
+        foreach ($entities as $entity) {
+            if (!isset($idsByType[$entity->type])) {
+                $idsByType[$entity->type] = [];
+            }
+            $idsByType[$entity->type][] = $entity->id;
+            $entityMap[$entity->type . ':' . $entity->id] = $entity;
+        }
+
+        $query = Tag::query();
+        foreach ($idsByType as $type => $ids) {
+            $query->orWhere(function ($query) use ($type, $ids) {
+                $query->where('entity_type', '=', $type)
+                    ->whereIn('entity_id', $ids);
+            });
+        }
+
+        $tags = empty($idsByType) ? [] : $query->get()->all();
+        $tagMap = [];
+        foreach ($tags as $tag) {
+            $key = $tag->entity_type . ':' . $tag->entity_id;
+            if (!isset($tagMap[$key])) {
+                $tagMap[$key] = [];
+            }
+            $tagMap[$key][] = $tag;
+        }
+
+        foreach ($entityMap as $key => $entity) {
+            $entityTags = new Collection($tagMap[$key] ?? []);
+            $entity->setRelation('tags', $entityTags);
+        }
+    }
+
+    /**
+     * @param Entity[] $entities
+     */
+    protected function loadParentsIntoModels(array $entities): void
+    {
+        $parentsByType = ['book' => [], 'chapter' => []];
+
+        foreach ($entities as $entity) {
+            if ($entity->getAttribute('book_id') !== null) {
+                $parentsByType['book'][] = $entity->getAttribute('book_id');
+            }
+            if ($entity->getAttribute('chapter_id') !== null) {
+                $parentsByType['chapter'][] = $entity->getAttribute('chapter_id');
+            }
+        }
+
+        // TODO - Inject in?
+        $queries = app()->make(EntityQueries::class);
+
+        $parentQuery = $queries->visibleForList();
+        $filtered = count($parentsByType['book']) > 0 || count($parentsByType['chapter']) > 0;
+        $parentQuery = $parentQuery->where(function ($query) use ($parentsByType) {
+            foreach ($parentsByType as $type => $ids) {
+                if (count($ids) > 0) {
+                    $query = $query->orWhere(function ($query) use ($type, $ids) {
+                        $query->where('type', '=', $type)
+                            ->whereIn('id', $ids);
+                    });
+                }
+            }
+        });
+
+        $parents = $filtered ? (new EntityHydrator($parentQuery->get()->all()))->hydrate() : [];
+        $parentMap = [];
+        foreach ($parents as $parent) {
+            $parentMap[$parent->type . ':' . $parent->id] = $parent;
+        }
+
+        foreach ($entities as $entity) {
+            if ($entity instanceof Page || $entity instanceof Chapter) {
+                $key = 'book:' . $entity->getAttribute('book_id');
+                $entity->setRelation('book', $parentMap[$key] ?? null);
+            }
+            if ($entity instanceof Page) {
+                $key = 'chapter:' . $entity->getAttribute('chapter_id');
+                $entity->setRelation('chapter', $parentMap[$key] ?? null);
+            }
+        }
+    }
+}

diff --git a/app/Entities/Tools/MixedEntityListLoader.php b/app/Entities/Tools/MixedEntityListLoader.php
index 0a0f224d86c65441d75dc799b5ca1eabbefd05d5..9987cc061ce17a0888026df067f470951f2f1039 100644 (file)
--- a/app/Entities/Tools/MixedEntityListLoader.php
+++ b/app/Entities/Tools/MixedEntityListLoader.php
@@ -54,7 +54,7 @@ class MixedEntityListLoader
         $modelMap = [];
 
         foreach ($idsByType as $type => $ids) {
-            $base = $withContents ? $this->queries->visibleForContent($type) : $this->queries->visibleForList($type);
+            $base = $withContents ? $this->queries->visibleForContentForType($type) : $this->queries->visibleForListForType($type);
             $models = $base->whereIn('id', $ids)
                 ->with($eagerLoadParents ? $this->getRelationsToEagerLoad($type) : [])
                 ->get();

diff --git a/app/Search/SearchRunner.php b/app/Search/SearchRunner.php
index a1ffeee50fd9c2f80d688282a1ee82900737ecb9..72ebe96bb4e8688455ae8a62045ea7860590a884 100644 (file)
--- a/app/Search/SearchRunner.php
+++ b/app/Search/SearchRunner.php
@@ -4,16 +4,16 @@ namespace BookStack\Search;
 
 use BookStack\Entities\EntityProvider;
 use BookStack\Entities\Models\Entity;
-use BookStack\Entities\Models\Page;
 use BookStack\Entities\Queries\EntityQueries;
+use BookStack\Entities\Tools\EntityHydrator;
 use BookStack\Permissions\PermissionApplicator;
 use BookStack\Search\Options\TagSearchOption;
 use BookStack\Users\Models\User;
 use Illuminate\Database\Connection;
 use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
 use Illuminate\Database\Eloquent\Collection as EloquentCollection;
-use Illuminate\Database\Eloquent\Relations\BelongsTo;
 use Illuminate\Database\Query\Builder;
+use Illuminate\Database\Query\JoinClause;
 use Illuminate\Support\Collection;
 use Illuminate\Support\Facades\DB;
 use Illuminate\Support\Str;
@@ -22,7 +22,7 @@ use WeakMap;
 class SearchRunner
 {
     /**
-     * Retain a cache of score adjusted terms for specific search options.
+     * Retain a cache of score-adjusted terms for specific search options.
      */
     protected WeakMap $termAdjustmentCache;
 
@@ -38,6 +38,7 @@ class SearchRunner
      * Search all entities in the system.
      * The provided count is for each entity to search,
      * Total returned could be larger and not guaranteed.
+     * // TODO - Update this comment
      *
      * @return array{total: int, count: int, has_more: bool, results: Collection<Entity>}
      */
@@ -53,26 +54,12 @@ class SearchRunner
             $entityTypesToSearch = explode('|', $filterMap['type']);
         }
 
-        $results = collect();
-        $total = 0;
-        $hasMore = false;
-
-        foreach ($entityTypesToSearch as $entityType) {
-            if (!in_array($entityType, $entityTypes)) {
-                continue;
-            }
+        $searchQuery = $this->buildQuery($searchOpts, $entityTypesToSearch);
+        $total = $searchQuery->count();
+        $results = $this->getPageOfDataFromQuery($searchQuery, $page, $count);
 
-            $searchQuery = $this->buildQuery($searchOpts, $entityType);
-            $entityTotal = $searchQuery->count();
-            $searchResults = $this->getPageOfDataFromQuery($searchQuery, $entityType, $page, $count);
-
-            if ($entityTotal > ($page * $count)) {
-                $hasMore = true;
-            }
-
-            $total += $entityTotal;
-            $results = $results->merge($searchResults);
-        }
+        // TODO - Pagination?
+        $hasMore = ($total > ($page * $count));
 
         return [
             'total'    => $total,
@@ -119,46 +106,41 @@ class SearchRunner
     /**
      * Get a page of result data from the given query based on the provided page parameters.
      */
-    protected function getPageOfDataFromQuery(EloquentBuilder $query, string $entityType, int $page = 1, int $count = 20): EloquentCollection
+    protected function getPageOfDataFromQuery(EloquentBuilder $query, int $page, int $count): Collection
     {
-        $relations = ['tags'];
-
-        if ($entityType === 'page' || $entityType === 'chapter') {
-            $relations['book'] = function (BelongsTo $query) {
-                $query->scopes('visible');
-            };
-        }
-
-        if ($entityType === 'page') {
-            $relations['chapter'] = function (BelongsTo $query) {
-                $query->scopes('visible');
-            };
-        }
-
-        return $query->clone()
-            ->with(array_filter($relations))
+        $entities = $query->clone()
+//            ->with(array_filter($relations))
             ->skip(($page - 1) * $count)
             ->take($count)
             ->get();
+
+        $hydrated = (new EntityHydrator($entities->all(), true, true))->hydrate();
+
+        // TODO - Load in books for pages/chapters efficiently (scoped to visible)
+        // TODO - Load in chapters for pages efficiently (scoped to visible)
+        // TODO - Load in tags efficiently
+
+        return collect($hydrated);
     }
 
     /**
      * Create a search query for an entity.
+     * @param string[] $entityTypes
      */
-    protected function buildQuery(SearchOptions $searchOpts, string $entityType): EloquentBuilder
+    protected function buildQuery(SearchOptions $searchOpts, array $entityTypes): EloquentBuilder
     {
-        $entityModelInstance = $this->entityProvider->get($entityType);
-        $entityQuery = $this->entityQueries->visibleForList($entityType);
+        $entityQuery = $this->entityQueries->visibleForList()
+            ->whereIn('type', $entityTypes);
 
         // Handle normal search terms
-        $this->applyTermSearch($entityQuery, $searchOpts, $entityType);
+        $this->applyTermSearch($entityQuery, $searchOpts, $entityTypes);
 
         // Handle exact term matching
         foreach ($searchOpts->exacts->all() as $exact) {
-            $filter = function (EloquentBuilder $query) use ($exact, $entityModelInstance) {
+            $filter = function (EloquentBuilder $query) use ($exact) {
                 $inputTerm = str_replace('\\', '\\\\', $exact->value);
                 $query->where('name', 'like', '%' . $inputTerm . '%')
-                    ->orWhere($entityModelInstance->textField, 'like', '%' . $inputTerm . '%');
+                    ->orWhere('description', 'like', '%' . $inputTerm . '%');
             };
 
             $exact->negated ? $entityQuery->whereNot($filter) : $entityQuery->where($filter);
@@ -173,7 +155,7 @@ class SearchRunner
         foreach ($searchOpts->filters->all() as $filterOption) {
             $functionName = Str::camel('filter_' . $filterOption->getKey());
             if (method_exists($this, $functionName)) {
-                $this->$functionName($entityQuery, $entityModelInstance, $filterOption->value, $filterOption->negated);
+                $this->$functionName($entityQuery, $filterOption->value, $filterOption->negated);
             }
         }
 
@@ -183,7 +165,7 @@ class SearchRunner
     /**
      * For the given search query, apply the queries for handling the regular search terms.
      */
-    protected function applyTermSearch(EloquentBuilder $entityQuery, SearchOptions $options, string $entityType): void
+    protected function applyTermSearch(EloquentBuilder $entityQuery, SearchOptions $options, array $entityTypes): void
     {
         $terms = $options->searches->toValueArray();
         if (count($terms) === 0) {
@@ -200,8 +182,6 @@ class SearchRunner
         ]);
 
         $subQuery->addBinding($scoreSelect['bindings'], 'select');
-
-        $subQuery->where('entity_type', '=', $entityType);
         $subQuery->where(function (Builder $query) use ($terms) {
             foreach ($terms as $inputTerm) {
                 $escapedTerm = str_replace('\\', '\\\\', $inputTerm);
@@ -210,7 +190,10 @@ class SearchRunner
         });
         $subQuery->groupBy('entity_type', 'entity_id');
 
-        $entityQuery->joinSub($subQuery, 's', 'id', '=', 'entity_id');
+        $entityQuery->joinSub($subQuery, 's', function (JoinClause $join) {
+            $join->on('s.entity_id', '=', 'entities.id')
+                ->on('s.entity_type', '=', 'entities.type');
+        });
         $entityQuery->addSelect('s.score');
         $entityQuery->orderBy('score', 'desc');
     }
@@ -350,31 +333,31 @@ class SearchRunner
     /**
      * Custom entity search filters.
      */
-    protected function filterUpdatedAfter(EloquentBuilder $query, Entity $model, string $input, bool $negated): void
+    protected function filterUpdatedAfter(EloquentBuilder $query, string $input, bool $negated): void
     {
         $date = date_create($input);
         $this->applyNegatableWhere($query, $negated, 'updated_at', '>=', $date);
     }
 
-    protected function filterUpdatedBefore(EloquentBuilder $query, Entity $model, string $input, bool $negated): void
+    protected function filterUpdatedBefore(EloquentBuilder $query, string $input, bool $negated): void
     {
         $date = date_create($input);
         $this->applyNegatableWhere($query, $negated, 'updated_at', '<', $date);
     }
 
-    protected function filterCreatedAfter(EloquentBuilder $query, Entity $model, string $input, bool $negated): void
+    protected function filterCreatedAfter(EloquentBuilder $query, string $input, bool $negated): void
     {
         $date = date_create($input);
         $this->applyNegatableWhere($query, $negated, 'created_at', '>=', $date);
     }
 
-    protected function filterCreatedBefore(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterCreatedBefore(EloquentBuilder $query, string $input, bool $negated)
     {
         $date = date_create($input);
         $this->applyNegatableWhere($query, $negated, 'created_at', '<', $date);
     }
 
-    protected function filterCreatedBy(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterCreatedBy(EloquentBuilder $query, string $input, bool $negated)
     {
         $userSlug = $input === 'me' ? user()->slug : trim($input);
         $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
@@ -383,7 +366,7 @@ class SearchRunner
         }
     }
 
-    protected function filterUpdatedBy(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterUpdatedBy(EloquentBuilder $query, string $input, bool $negated)
     {
         $userSlug = $input === 'me' ? user()->slug : trim($input);
         $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
@@ -392,7 +375,7 @@ class SearchRunner
         }
     }
 
-    protected function filterOwnedBy(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterOwnedBy(EloquentBuilder $query, string $input, bool $negated)
     {
         $userSlug = $input === 'me' ? user()->slug : trim($input);
         $user = User::query()->where('slug', '=', $userSlug)->first(['id']);
@@ -401,27 +384,27 @@ class SearchRunner
         }
     }
 
-    protected function filterInName(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterInName(EloquentBuilder $query, string $input, bool $negated)
     {
         $this->applyNegatableWhere($query, $negated, 'name', 'like', '%' . $input . '%');
     }
 
-    protected function filterInTitle(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterInTitle(EloquentBuilder $query, string $input, bool $negated)
     {
-        $this->filterInName($query, $model, $input, $negated);
+        $this->filterInName($query, $input, $negated);
     }
 
-    protected function filterInBody(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterInBody(EloquentBuilder $query, string $input, bool $negated)
     {
-        $this->applyNegatableWhere($query, $negated, $model->textField, 'like', '%' . $input . '%');
+        $this->applyNegatableWhere($query, $negated, 'description', 'like', '%' . $input . '%');
     }
 
-    protected function filterIsRestricted(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterIsRestricted(EloquentBuilder $query, string $input, bool $negated)
     {
         $negated ? $query->whereDoesntHave('permissions') : $query->whereHas('permissions');
     }
 
-    protected function filterViewedByMe(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterViewedByMe(EloquentBuilder $query, string $input, bool $negated)
     {
         $filter = function ($query) {
             $query->where('user_id', '=', user()->id);
@@ -430,7 +413,7 @@ class SearchRunner
         $negated ? $query->whereDoesntHave('views', $filter) : $query->whereHas('views', $filter);
     }
 
-    protected function filterNotViewedByMe(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterNotViewedByMe(EloquentBuilder $query, string $input, bool $negated)
     {
         $filter = function ($query) {
             $query->where('user_id', '=', user()->id);
@@ -439,31 +422,30 @@ class SearchRunner
         $negated ? $query->whereHas('views', $filter) : $query->whereDoesntHave('views', $filter);
     }
 
-    protected function filterIsTemplate(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterIsTemplate(EloquentBuilder $query, string $input, bool $negated)
     {
-        if ($model instanceof Page) {
-            $this->applyNegatableWhere($query, $negated, 'template', '=', true);
-        }
+        $this->applyNegatableWhere($query, $negated, 'template', '=', true);
     }
 
-    protected function filterSortBy(EloquentBuilder $query, Entity $model, string $input, bool $negated)
+    protected function filterSortBy(EloquentBuilder $query, string $input, bool $negated)
     {
         $functionName = Str::camel('sort_by_' . $input);
         if (method_exists($this, $functionName)) {
-            $this->$functionName($query, $model, $negated);
+            $this->$functionName($query, $negated);
         }
     }
 
     /**
      * Sorting filter options.
      */
-    protected function sortByLastCommented(EloquentBuilder $query, Entity $model, bool $negated)
+    protected function sortByLastCommented(EloquentBuilder $query, bool $negated)
     {
         $commentsTable = DB::getTablePrefix() . 'comments';
-        $morphClass = str_replace('\\', '\\\\', $model->getMorphClass());
-        $commentQuery = DB::raw('(SELECT c1.commentable_id, c1.commentable_type, c1.created_at as last_commented FROM ' . $commentsTable . ' c1 LEFT JOIN ' . $commentsTable . ' c2 ON (c1.commentable_id = c2.commentable_id AND c1.commentable_type = c2.commentable_type AND c1.created_at < c2.created_at) WHERE c1.commentable_type = \'' . $morphClass . '\' AND c2.created_at IS NULL) as comments');
+        $commentQuery = DB::raw('(SELECT c1.commentable_id, c1.commentable_type, c1.created_at as last_commented FROM ' . $commentsTable . ' c1 LEFT JOIN ' . $commentsTable . ' c2 ON (c1.commentable_id = c2.commentable_id AND c1.commentable_type = c2.commentable_type AND c1.created_at < c2.created_at) WHERE c2.created_at IS NULL) as comments');
 
-        $query->join($commentQuery, $model->getTable() . '.id', '=', DB::raw('comments.commentable_id'))
-            ->orderBy('last_commented', $negated ? 'asc' : 'desc');
+        $query->join($commentQuery, function (JoinClause $join) {
+            $join->on('entities.id', '=', 'comments.commentable_id')
+                ->on('entities.type', '=', 'comments.commentable_type');
+        })->orderBy('last_commented', $negated ? 'asc' : 'desc');
     }
 }