Skip to content

Commit a70ed81

Browse files
ssddanbrownssddanbrown
committed
DB: Started update of entity loading to avoid global selects
Removes page/chpater addSelect global query, to load book slug, and instead extracts base queries to be managed in new static class, while updating specific entitiy relation loading to use our more efficient MixedEntityListLoader where appropriate. Related to #4823
1 parent 2460e7c commit a70ed81

10 files changed

Lines changed: 115 additions & 41 deletions

File tree

‎app/Activity/ActivityQueries.php‎

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -7,18 +7,18 @@
77
use BookStack\Entities\Models\Chapter;
88
use BookStack\Entities\Models\Entity;
99
use BookStack\Entities\Models\Page;
10+
use BookStack\Entities\Tools\MixedEntityListLoader;
1011
use BookStack\Permissions\PermissionApplicator;
1112
use BookStack\Users\Models\User;
1213
use Illuminate\Database\Eloquent\Builder;
1314
use Illuminate\Database\Eloquent\Relations\Relation;
1415

1516
class ActivityQueries
1617
{
17-
protected PermissionApplicator $permissions;
18-
19-
public function __construct(PermissionApplicator $permissions)
20-
{
21-
$this->permissions = $permissions;
18+
public function __construct(
19+
protected PermissionApplicator $permissions,
20+
protected MixedEntityListLoader $listLoader,
21+
) {
2222
}
2323

2424
/**
@@ -29,11 +29,13 @@ public function latest(int $count = 20, int $page = 0): array
2929
$activityList = $this->permissions
3030
->restrictEntityRelationQuery(Activity::query(), 'activities', 'entity_id', 'entity_type')
3131
->orderBy('created_at', 'desc')
32-
->with(['user', 'entity'])
32+
->with(['user'])
3333
->skip($count * $page)
3434
->take($count)
3535
->get();
3636

37+
$this->listLoader->loadIntoRelations($activityList->all(), 'entity', false);
38+
3739
return $this->filterSimilar($activityList);
3840
}
3941

‎app/App/HomeController.php‎

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55
use BookStack\Activity\ActivityQueries;
66
use BookStack\Entities\Models\Book;
77
use BookStack\Entities\Models\Page;
8+
use BookStack\Entities\Queries\PageQueries;
89
use BookStack\Entities\Queries\RecentlyViewed;
910
use BookStack\Entities\Queries\TopFavourites;
1011
use BookStack\Entities\Repos\BookRepo;
@@ -26,9 +27,7 @@ public function index(Request $request, ActivityQueries $activities)
2627
$draftPages = [];
2728

2829
if ($this->isSignedIn()) {
29-
$draftPages = Page::visible()
30-
->where('draft', '=', true)
31-
->where('created_by', '=', user()->id)
30+
$draftPages = PageQueries::currentUserDraftsForList()
3231
->orderBy('updated_at', 'desc')
3332
->with('book')
3433
->take(6)
@@ -40,11 +39,10 @@ public function index(Request $request, ActivityQueries $activities)
4039
(new RecentlyViewed())->run(12 * $recentFactor, 1)
4140
: Book::visible()->orderBy('created_at', 'desc')->take(12 * $recentFactor)->get();
4241
$favourites = (new TopFavourites())->run(6);
43-
$recentlyUpdatedPages = Page::visible()->with('book')
42+
$recentlyUpdatedPages = PageQueries::visibleForList()
4443
->where('draft', false)
4544
->orderBy('updated_at', 'desc')
4645
->take($favourites->count() > 0 ? 5 : 10)
47-
->select(Page::$listAttributes)
4846
->get();
4947

5048
$homepageOptions = ['default', 'books', 'bookshelves', 'page'];
@@ -95,7 +93,7 @@ public function index(Request $request, ActivityQueries $activities)
9593
$homepageSetting = setting('app-homepage', '0:');
9694
$id = intval(explode(':', $homepageSetting)[0]);
9795
/** @var Page $customHomepage */
98-
$customHomepage = Page::query()->where('draft', '=', false)->findOrFail($id);
96+
$customHomepage = PageQueries::start()->where('draft', '=', false)->findOrFail($id);
9997
$pageContent = new PageContent($customHomepage);
10098
$customHomepage->html = $pageContent->render(false);
10199

‎app/Entities/Models/BookChild.php‎

Lines changed: 0 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -18,20 +18,6 @@
1818
*/
1919
abstract class BookChild extends Entity
2020
{
21-
protected static function boot()
22-
{
23-
parent::boot();
24-
25-
// Load book slugs onto these models by default during query-time
26-
static::addGlobalScope('book_slug', function (Builder $builder) {
27-
$builder->addSelect(['book_slug' => function ($builder) {
28-
$builder->select('slug')
29-
->from('books')
30-
->whereColumn('books.id', '=', 'book_id');
31-
}]);
32-
});
33-
}
34-
3521
/**
3622
* Scope a query to find items where the child has the given childSlug
3723
* where its parent has the bookSlug.

‎app/Entities/Queries/EntityQuery.php‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,10 +3,16 @@
33
namespace BookStack\Entities\Queries;
44

55
use BookStack\Entities\EntityProvider;
6+
use BookStack\Entities\Tools\MixedEntityListLoader;
67
use BookStack\Permissions\PermissionApplicator;
78

89
abstract class EntityQuery
910
{
11+
protected function mixedEntityListLoader(): MixedEntityListLoader
12+
{
13+
return app()->make(MixedEntityListLoader::class);
14+
}
15+
1016
protected function permissionService(): PermissionApplicator
1117
{
1218
return app()->make(PermissionApplicator::class);

‎app/Entities/Queries/PageQueries.php‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
<?php
2+
3+
namespace BookStack\Entities\Queries;
4+
5+
use BookStack\Entities\Models\Page;
6+
use Illuminate\Database\Eloquent\Builder;
7+
8+
class PageQueries
9+
{
10+
public static function start(): Builder
11+
{
12+
return Page::query();
13+
}
14+
15+
public static function visibleForList(): Builder
16+
{
17+
return Page::visible()
18+
->select(array_merge(Page::$listAttributes, ['book_slug' => function ($builder) {
19+
$builder->select('slug')
20+
->from('books')
21+
->whereColumn('books.id', '=', 'pages.book_id');
22+
}]));
23+
}
24+
25+
public static function currentUserDraftsForList(): Builder
26+
{
27+
return static::visibleForList()
28+
->where('draft', '=', true)
29+
->where('created_by', '=', user()->id);
30+
}
31+
}

‎app/Entities/Queries/RecentlyViewed.php‎

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ class RecentlyViewed extends EntityQuery
1010
public function run(int $count, int $page): Collection
1111
{
1212
$user = user();
13-
if ($user === null || $user->isGuest()) {
13+
if ($user->isGuest()) {
1414
return collect();
1515
}
1616

@@ -23,11 +23,13 @@ public function run(int $count, int $page): Collection
2323
->orderBy('views.updated_at', 'desc')
2424
->where('user_id', '=', user()->id);
2525

26-
return $query->with('viewable')
26+
$views = $query
2727
->skip(($page - 1) * $count)
2828
->take($count)
29-
->get()
30-
->pluck('viewable')
31-
->filter();
29+
->get();
30+
31+
$this->mixedEntityListLoader()->loadIntoRelations($views->all(), 'viewable', false);
32+
33+
return $views->pluck('viewable')->filter();
3234
}
3335
}

‎app/Entities/Queries/TopFavourites.php‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -25,11 +25,13 @@ public function run(int $count, int $skip = 0)
2525
->orderBy('views.views', 'desc')
2626
->where('favourites.user_id', '=', user()->id);
2727

28-
return $query->with('favouritable')
28+
$favourites = $query
2929
->skip($skip)
3030
->take($count)
31-
->get()
32-
->pluck('favouritable')
33-
->filter();
31+
->get();
32+
33+
$this->mixedEntityListLoader()->loadIntoRelations($favourites->all(), 'favouritable', false);
34+
35+
return $favourites->pluck('favouritable')->filter();
3436
}
3537
}

‎app/Entities/Tools/MixedEntityListLoader.php‎

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@ public function __construct(
2626
* This will look for a model id and type via 'name_id' and 'name_type'.
2727
* @param Model[] $relations
2828
*/
29-
public function loadIntoRelations(array $relations, string $relationName): void
29+
public function loadIntoRelations(array $relations, string $relationName, bool $loadParents): void
3030
{
3131
$idsByType = [];
3232
foreach ($relations as $relation) {
@@ -40,7 +40,7 @@ public function loadIntoRelations(array $relations, string $relationName): void
4040
$idsByType[$type][] = $id;
4141
}
4242

43-
$modelMap = $this->idsByTypeToModelMap($idsByType);
43+
$modelMap = $this->idsByTypeToModelMap($idsByType, $loadParents);
4444

4545
foreach ($relations as $relation) {
4646
$type = $relation->getAttribute($relationName . '_type');
@@ -56,7 +56,7 @@ public function loadIntoRelations(array $relations, string $relationName): void
5656
* @param array<string, int[]> $idsByType
5757
* @return array<string, array<int, Model>>
5858
*/
59-
protected function idsByTypeToModelMap(array $idsByType): array
59+
protected function idsByTypeToModelMap(array $idsByType, bool $eagerLoadParents): array
6060
{
6161
$modelMap = [];
6262

@@ -67,10 +67,10 @@ protected function idsByTypeToModelMap(array $idsByType): array
6767

6868
$instance = $this->entityProvider->get($type);
6969
$models = $instance->newQuery()
70-
->select($this->listAttributes[$type])
70+
->select(array_merge($this->listAttributes[$type], $this->getSubSelectsForQuery($type)))
7171
->scopes('visible')
7272
->whereIn('id', $ids)
73-
->with($this->getRelationsToEagerLoad($type))
73+
->with($eagerLoadParents ? $this->getRelationsToEagerLoad($type) : [])
7474
->get();
7575

7676
if (count($models) > 0) {
@@ -100,4 +100,19 @@ protected function getRelationsToEagerLoad(string $type): array
100100

101101
return $toLoad;
102102
}
103+
104+
protected function getSubSelectsForQuery(string $type): array
105+
{
106+
$subSelects = [];
107+
108+
if ($type === 'chapter' || $type === 'page') {
109+
$subSelects['book_slug'] = function ($builder) {
110+
$builder->select('slug')
111+
->from('books')
112+
->whereColumn('books.id', '=', 'book_id');
113+
};
114+
}
115+
116+
return $subSelects;
117+
}
103118
}

‎app/References/ReferenceFetcher.php‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ public function __construct(
2323
public function getReferencesToEntity(Entity $entity): Collection
2424
{
2525
$references = $this->queryReferencesToEntity($entity)->get();
26-
$this->mixedEntityListLoader->loadIntoRelations($references->all(), 'from');
26+
$this->mixedEntityListLoader->loadIntoRelations($references->all(), 'from', true);
2727

2828
return $references;
2929
}

‎database/migrations/2024_02_04_141358_add_views_updated_index.php‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
<?php
2+
3+
use Illuminate\Database\Migrations\Migration;
4+
use Illuminate\Database\Schema\Blueprint;
5+
use Illuminate\Support\Facades\Schema;
6+
7+
return new class extends Migration
8+
{
9+
/**
10+
* Run the migrations.
11+
*
12+
* @return void
13+
*/
14+
public function up()
15+
{
16+
Schema::table('views', function (Blueprint $table) {
17+
$table->index(['updated_at'], 'views_updated_at_index');
18+
});
19+
}
20+
21+
/**
22+
* Reverse the migrations.
23+
*
24+
* @return void
25+
*/
26+
public function down()
27+
{
28+
Schema::table('views', function (Blueprint $table) {
29+
$table->dropIndex('views_updated_at_index');
30+
});
31+
}
32+
};

0 commit comments

Comments
 (0)