Database access from within block

The CMS Questions
1

Hello

I would like to read database (sqlite) records from within a LayoutBuilder block in order to embed those database records on different pages together with other LayoutBuilder blocks. I am using a third party Layout Theme (implemented as Plugin) by extending this with my db block.

I created the block php file within site/theme/snippets/blocks and can access the database records and render the entries by :

$records = Db::select('projects');

“Unfortunately” in the rendering part “foreach($records as $record): ... $record->project_title() ...” I can not use in this context useful methods like ->html(), ->url() etc. thus $record->project_title()->html()

I tried also from the database guide to use part of the model code to create a virtual page structure like :

foreach (Db::select('projects') as $record) {
            $records[] = [
                'slug'     => $record->project_id(),
                'num'      => 0,
                # 'template' => 'comment',
                # 'model'    => 'comment',
                'content'  => [
                    'title'  => $record->project_title(),
                    'text'  => $record->project_web_description(),
                    'url'  => $record->project_link()',
                    'uuid'  => Uuid::generate(),
                ]
            ];
        };

This represents an array but not a page/children object. I can not figure out how to to convert this array to a valid page/children object within the block code.

At the moment I am not sure if I have to implement a fully virtual page structure or I can go with the simple database access approach.

My aim is the following:

  • a “general purpose” LayoutBuilder block with database access (read only) for different tables
  • an appropriate block panel blueprint to define main parameters of the block like db “table”, db select parameters etc. and styling/rendering parameters.
  • I do not want to show/edit the single records in the panel (as the database records a part of an external PM-system).
  • I would like to use kirby tags, created on the fly with the database select/query statements (virtual db fields) to be able to filter records by tags (within the same block). Thus user should be able to filter records by clicking on the presented tags-list.

I would appreciate any hint for a suitable approach. Many thanks in advance.

André

2

What do these properties return if you dump() them? Instead of the field methods (html() etc), which require a field object, you can use the helpers html($record->project_id())

3

Many thanks for your reponse.

by using $records = Db::select('projects');

  • dump($records) gives a Kirby\Toolkit\Collection Object.
  • dump($record) gives a Kirby\Toolkit\Obj Object.
  • dump($record->project_id()) gives the expected db field value (no Object type specified).

I mentioned in my first message, that I would like to use the Kirby Tags functionality within my “db” block. As I can see from the cookbook topic about Tag filtering filtering is working on a (page?) collection. I assume that $records is representing a collection that could be filtered if there is a field with the name “tags”. But actually I can not see how I could pass the tag url parameter to a page in which my “db” block or multiple “db” blocks is/are embeded? I assume in the case of multiple enbeded “db” blocks in the same page I could specify different/unique url tag parameters (e.g. …/block1tag:house&block2tag:flower ). Would that approach work?

Many thanks again

4

About my question with tags I tested the following:

  • every db field present in the $records collection can be used as filter (e.g. $records->filterBy('project_v_type', $tag1, ',');.
  • by using an unique url parameter name for a tag for each “db” block embeded in the same page each single “db” block can “intercept” its corresponding tag parameter (e.g. …/block1tag:house/block2tag:flower ).
  • the url parameter name tag can be part of the corresponding “db” block panel definition and thus be retrieved in the “db” block code.