Caching SQL results via API cache

The CMS Questions
1

hi together
im on a caching task and im not sure i use it properly. maybe you guys have an idea. i have a big sql lite table (200’000 entries) and im want caching results from this. im using the basic api cache pattern like this.

 if ($apiData === null) {
        $apiData = Db::select(fancyness sql); ->> returns 200 rows
        $apiCache->set('apiData', $apiData);
 }

i got an array back with all demanded rows. because im using the result to fetch DB entries i have to convert the array back to an proper kirby object.

$prepData = new Obj($apiCache['data']);

and later in the loop each item

 foreach ($prepData as $item)  {
    $item = new Obj($item);
    echo $item->entry_id()
    ...
}

after this, the cache is working properly and i can access all items like $item->entry_id() in the loop.

now, my questions:

  1. it this a proper way to cache DB rows?
  2. DB:select return in the basic usage a prepared kirby collection, and all items are class Obj. Is there a better way to convert the array in my workflow?

thanks

2

seems fine. you skipped posting the cache get call but i assume its before the if clause.

you could use a fixed timeout for the cache.

afaik the db query class can be configured to return an array not an object and that might save some microseconds.

3

Thanks for the tipps. i skipped some parts in the code your right. and i will set the exp. date to 15min or so.

how can i set the fetch option? i think the cache api returns already an array so im not sure this is necessary?

$apiDataDrawing = Db::select(fancyness sql);
$apiDataDrawing->fetch('array');
4

Is there any example how to use this? I can’t figure out:

  $apiDataTrendQuery = Db::query(
        'SELECT title,text
        FROM y_entry 
        INNER JOIN y_user 
        ON y_entry.entry_sender == y_user.id
        WHERE y_user.active == "1"
        ORDER BY y_entry.entry_creationDate DESC 
        LIMIT 100'
    );

SQL is fine, i get all data, but as Objects. I can use ->toArray() to convert the Collection. But how i can use fetch() in this case?

5

You can pass that as option in the third method argument,

Db::query($queryString, [], ['fetch' => 'array'])
6

Hey nice thank you, works now. In my case i needed all array, so variant d:
Maybe something for the docs to clarify who this works.

a:

Db::query($queryString);

Kirby\Toolkit\Collection Object
(
    [0] => 0
    [1] => 1

b:

Db::query($queryString)->toArray();

Array
(
    [0] => Kirby\Toolkit\Obj Object
        (
            //my data
        )
 	[1] => Kirby\Toolkit\Obj Object
        (
            //my data
        )

c:

Db::query($queryString, [], ['fetch' => 'array'])

Kirby\Toolkit\Collection Object
(
    [0] => 0
    [1] => 1

d:

 Db::query($queryString, [], ['fetch' => 'array'])->toArray();

Array
(
    [0] => Array
        (
           // my data 
        )

    [1] => Array
        (
			// my data
        )