Increase performance of query

[schneyra]

Hey! I’ve build a little list of related blogpost, you can find it here for example in this post at the very bottom, headlined “Weeknotes aus Woche 01”.

My goal is to get other posts that I’ve written in the same week of the year. My working code looks like this:

$thisWeekInThePast = page('blog')
      ->grandChildren()->children()
      ->filter(function ($page) use ($week) {
          return in_array('allwoechentlich-belangloses', $page->categories()->toArray()) 
                 && DtuiHelper::getWeekFromDate($page->date()) == $week;
      });

Although it works, I experience longer load times now, especially on the overview pages where several of these lists can be generated. Ich have about 1600 pages in total and about 250 of them match the category in question.

What opportunities to I have to speed things a bit up? I’ve just learned about collections while writing this. Would it speed up my query if I put all posts of that category in a collection? Do I have any other opportunities to speed things up?

[bnomei]

there is not much you can do to to optimize that query. i would add a cache around the query.

Panel speed / cache?

@diezmilseres serialization does NOT work out-of-the-box. you need to recreate the pages collection yourself. but since kirby is clever enough to do that from an array of ids the code from above can be made shorter see <!--------. <?php class AutorizacionesPage extends Kirby\Cms\Page { public function historial(): Kirby\Cms\Pages { $data = Bnomei\Lapse::io( 'AutorizacionesHistorial', function () { $collectionForIds = pages('afiliados') ->grandChildren() …

or add a global content cache to speed up loading of pages.

GitHub - bnomei/kirby3-boost: Boost the speed of Kirby by having content...

[bnomei]

instead of “filtering” you could do the reverse and keep a “lookup-table” which you keep up-to-date when page objects change.

1. on each page update use the hook to write the page id into a cached array. one array for each category or only for ‘allwoechentlich-belangloses’ if thats the only one you need. add the aggregated week number as well
2. instead of performing the query do load the cached array for that category and make a page collection from the stored ids filtered by week

// 1 create a plugin with a cache
// 2 add a hook and write lookup-table to cache
Kirby::plugin('myplugin.createme', [
   'options' => ['cache' => true],
   'hooks' => [
        'page.update:after' => function (Kirby\Cms\Page $newPage, Kirby\Cms\Page $oldPage) {
            if($page->template()->name() == 'yourchoice') {
                $idAndWeeks = kirby()->cache('myplugin.createme')->get('allwoechentlich-belangloses', []);
                $idAndWeeks[$newPage->id()] = DtuiHelper::getWeekFromDate($newPage->date());
                kirby()->cache('myplugin.createme')->set('allwoechentlich-belangloses', $idAndWeeks);
            }
        }
    ]
]);

// 3 finally read cache and get pages
$idAndWeeks = kirby()->cache('myplugin.createme')->get('allwoechentlich-belangloses', []);
// if array is empty you need to perform the query once and store it. the cache was flushed (or there are no results)
if (count($idAndWeeks) === 0) {
    $idAndWeeks = page('blog')
      ->grandChildren()->children()
      ->filterBy('categories', 'allwoechentlich-belangloses' , ',')
      ->toArray(fn($p) => DtuiHelper::getWeekFromDate($p->date());
    // array of $id => $week pairs
    kirby()->cache('myplugin.createme')->set('allwoechentlich-belangloses', $idAndWeeks);
}
foreach($idAndWeeks as $pageid => $pageweek) {
   if ($week !== $pageweek) continue;
   $thisWeekInThePast[] = $pageid;
}
$thisWeekInThePast = Pages::factory($thisWeekInThePast); // array to pages collection

[schneyra]

Wow, thanks for your answer and sorry for returning to this thread so late. I’m just working in my spare time on my page and sometimes time flies. So much to learn about Kirby for me!

I’ll definetly have a deeper look at the caching functions, they might come in handy since I also have some external request that might be cached as well.