Create page model using information from database

paulrobertlloyd · October 17, 2017, 11:06pm

Hello,

I have a feeling I may be attempting the impossible here, but I shall ask anyway!

Part of the site I’m building has many 1000’s of pages containing a certain type of content, with data of a length and format that would be easier to manage in a database. I have successfully transferred this data from the existing flat content files, into a SQLite database, and via a route, been able to query it, and populate a template.

// config/config.php
array(
    'pattern' => 'stations/(:any)',
    'method' => 'GET',
    'action' => function ($uid) {
        $db = new Database(array(
            'type'     => c::get('db.type'),
            'database' => c::get('db.database')
        ));
        $stations = $db->table('stations');
        $station = $stations->where('uid', '=', $uid)->first();

        if (!$station) {
            return site()->errorPage();
        } else {
            tpl::load(kirby()->roots()->templates().DS.'station.php', [
                'site' => kirby()->site(),
                'page' => $station
            ], false);
        }
    }
)

You’ll not that I’m pointing $page to the $station database row. This means, that in my template, I can write the following:

// templates/station.php
<?= $page->title() ?>

The problem I’m running into now, as I further integrate this data into the rest of my site, is that as soon as I include any snippets or patterns that use queries like empty(), I get errors such as:

Call to a member function empty() on null

Or when I use $page->isHomePage(), my template breaks. In both cases I think Kirby is expecting a true or false value to be returned, but I’m returning… I’m not sure, but null, I suspect.

I thought this issue could be solved by creating a new page model (StationPage), and populating this with the values my snippets expect. Yet, from my explorations, it looks as though routes don’t touch page models if there’s no corresponding content file. Is that correct?

Anyway, what I’m hoping to achieve, is to create the page object that Kirby expects, but ‘merged’ with data obtained from a database (and my own hard-coded values), all without needing to create corresponding content files. Is this possible? Please, please say it is!!

Thanks,

Paul

texnixe · October 18, 2017, 6:17am

Unfortunately, the Page class is bound to the file system, as you can see from its constructor:

public function __construct($parent, $dirname) {

    $this->kirby   = $parent->kirby;
    $this->site    = $parent->site;
    $this->parent  = $parent;
    $this->dirname = $dirname;
    $this->root    = $parent->root() . DS . $dirname;
    $this->depth   = $parent->depth() + 1;

    // extract the uid and num of the directory
    if(preg_match('/^([0-9]+)[\-](.*)$/', $this->dirname, $match)) {
      $this->uid = $match[2];
      $this->num = $match[1];
    } else {
      $this->num = null;
      $this->uid = $this->dirname;
    }

    // assign the uid
    $this->id = $this->uri = ltrim($parent->id() . '/' . $this->uid, '/');

  }

So that means, to create a page object, you need a parent page and a directory name. Why don’t you just work with the data array you get from the database?

bvdputte · October 18, 2017, 6:57am

Hello, I think I have encountered the same issue as you are referring to, but then with reading page-data from a JSON file instead of from a database.

I’ve worked around it like this:

Add a dummy folder named dummy to /content/stations/, with inside it a station.en.md (or station.txt, without the language code if it is a single language installation and maybe the .txt extension) file. Now you should be able to visit that page if you enter its URL into a browser
Add a blueprint that hides it in the panel (if you use the panel, that is)
Set up the route as you did, but add this in the action:

    array( 
        'pattern' => 'stations/(:any)',
        'method' => 'GET',
        'action'  => function($uid) {
            // Get a working page object, with correct URL etc
            $stationPage = site()->visit("stations/dummy");

            $db = new Database(array(
                'type'     => c::get('db.type'),
                'database' => c::get('db.database')
            ));
            $stations = $db->table('stations');
            $station = $stations->where('uid', '=', $uid)->first();

            if (!$station) {
                return site()->errorPage();
            } else {
                $extrafields = array(
                    'fieldone' => $station->fieldone(),
                    'fieldtwo'  => $station->fieldtwo(),
                    'fieldthree'  => $station->fieldthree()
                    // And so on...
                );

                // Add extra fields from database row to our Dummy page object
                tpl::$data = array_merge(tpl::$data, array(
                    'kirby' => kirby(),
                    'site' => site(),
                    'pages' => site()->children(),
                    'page' => $stationPage
                ), $extrafields);

                // Load template with data and display it
                echo tpl::load(kirby()->roots()->templates().DS.'station.php', $extrafields);
                // Break route
                return false;
            }
        }
    )

This piece of code is adjusted to your use case, and not tested. But it should give you an insight in how I did it.

Good luck!

texnixe · October 18, 2017, 7:12am

I don’t think you need to load the template if you actually visit an existing page. You should be able to just pass the data to the $site->visit() call, like this:


array( 
        'pattern' => 'stations/(:any)',
        'method' => 'GET',
        'action'  => function($uid) {
            $db = new Database(array(
                'type'     => c::get('db.type'),
                'database' => c::get('db.database')
            ));
            $stations = $db->table('stations');
            $station = $stations->where('uid', '=', $uid)->first();

            if (!$station) {
                return site()->errorPage();
            } else {
                $extrafields = array(
                    'fieldone' => $station->fieldone(),
                    'fieldtwo'  => $station->fieldtwo(),
                    'fieldthree'  => $station->fieldthree()
                    // And so on...
                );
                // Load template with data and display it
                return array(site()->visit('stations/dummy'), $extrafields);
            }
        }
    )

You can then grab the additional data in your controller using the fourth argument.

bvdputte · October 18, 2017, 7:18am

@texnixe, I don’t know. I had to visit the page first anyway, because I needed multilang

texnixe · October 18, 2017, 7:20am

This should work with multilang as well, if you pass the language

return array(site()->visit('stations/dummy', 'en'), $extrafields);

But you can do this in two steps as well:

site()->visit('stations/dummy', 'en');
return array('stations/dummy', $extrafields);

All I was trying to say is that you don’t need to load the template, because the correct template is loaded via the dummy page anyway.

paulrobertlloyd · October 18, 2017, 11:39am

Thanks so much for the fast and (hopefully) helpful responses! I will try this out this evening, and let you know how I go. Thanks again!

paulrobertlloyd · October 18, 2017, 9:05pm

Unfortunately, neither of these approaches seem to work, as far as I can tell. I was also hoping that I could still use values like $page->title(), which would refer to the title saved in the database, but by the looks of this, the title would have to come from the $extrafields array.

Putting aside the requirement not to have a flat content file, is there a way I can visit/read that dummy file, and then overwrite parts of the Page Object with new values sourced from the DB?

bvdputte · October 18, 2017, 9:09pm

I believe you can? If the field is in the database, you can add it to the $extrafields array and then it get merged in via array_merge (even the title field)?

paulrobertlloyd · October 18, 2017, 9:16pm

This is what I have currently in my route:

array(
    'pattern' => 'stations/(:any)',
    'method' => 'GET',
    'action' => function ($uid) {
        $db = new Database(array(
            'type'     => c::get('db.type'),
            'database' => c::get('db.database')
        ));
        $stations = $db->table('stations');
        $station = $stations->where('uid', '=', $uid)->first();

        if (!$station) {
            return site()->errorPage();
        } else {
            $extrafields = array(
                'uid' => $station->uid(),
                'title'  => $station->title(),
                'country' => $station->country(),
                'region' => $station->region(),
                'place'  => $station->place(),
                'location'  => [
                    (float) $station->geolng(),
                    (float) $station->geolat()
                ]
            );

            tpl::$data = array_merge(tpl::$data, array(
                'kirby' => kirby(),
                'site' => site(),
                'pages' => site()->children(),
                'page' => site()->visit('stations/station')
            ), $extrafields);

            // Load template with data and display it
            echo tpl::load(kirby()->roots()->templates().DS.'station.php', $extrafields);

            // Break route
            return false;
        }
    }
}

In my template, I have the following:

a::show($page);

and this is the output:

Page Object
(
    [title] => station
    [id] => stations/station
    [uid] => station
    [slug] => station
    [parent] => stations
    [uri] => stations/station
    [url] => https://bradshaws.test/stations/station
    [contentUrl] => https://bradshaws.test/content/stations/station
    [tinyUrl] => http://bradshaws.test/1coy4l9
    [root] => ../src/content/stations/station
    [dirname] => station
    [diruri] => stations/station
    [depth] => 2
    [num] => 
    [hash] => 1coy4l9
    [modified] => 2017-10-18T19:56:39+00:00
    [template] => station
    [intendedTemplate] => station
    [isVisible] => 
    [isOpen] => 1
    [isActive] => 1
    [isHomePage] => 
    [isErrorPage] => 
    [isCachable] => 1
    [isWritable] => 1
    [content] => Content Object
        (
            [root] => ../src/content/stations/station/station.md
            [fields] => Array
                (
                )

        )

    [headers] => 
    [children] => Children Object
        (
        )

    [siblings] => Children Object
        (
        )

    [files] => Files Object
        (
        )

)

Is this the result you were expecting?

bvdputte · October 18, 2017, 9:19pm

Could you var_dump($page) (from the template) and var_dump($station) (from the controller) please?

paulrobertlloyd · October 18, 2017, 9:24pm

var_dump($page) from the template:

object(Page)#176 (30) { ["title"]=> string(7) "station" ["id"]=> string(16) "stations/station" ["uid"]=> string(7) "station" ["slug"]=> string(7) "station" ["parent"]=> string(8) "stations" ["uri"]=> string(16) "stations/station" ["url"]=> string(39) "https://bradshaws.test/stations/station" ["contentUrl"]=> string(47) "https://bradshaws.test/content/stations/station" ["tinyUrl"]=> string(30) "https://bradshaws.test/1coy4l9" ["root"]=> string(31) "../src/content/stations/station" ["dirname"]=> string(7) "station" ["diruri"]=> string(16) "stations/station" ["depth"]=> int(2) ["num"]=> NULL ["hash"]=> string(7) "1coy4l9" ["modified"]=> string(25) "2017-10-18T19:56:39+00:00" ["template"]=> string(7) "station" ["intendedTemplate"]=> string(7) "station" ["isVisible"]=> bool(false) ["isOpen"]=> bool(true) ["isActive"]=> bool(true) ["isHomePage"]=> bool(false) ["isErrorPage"]=> bool(false) ["isCachable"]=> bool(true) ["isWritable"]=> bool(true) ["content"]=> object(Content)#177 (2) { ["root"]=> string(42) "../src/content/stations/station/station.md" ["fields"]=> array(0) { } } ["headers"]=> NULL ["children"]=> object(Children)#178 (0) { } ["siblings"]=> object(Children)#182 (0) { } ["files"]=> object(Files)#179 (0) { } }

How should I be writing my controller? Do I need one with this approach?

bvdputte · October 18, 2017, 9:38pm

Sorry, I meant “route”, not “controller”.

Anyway, I’ve re-checked my code and now I see I store all the “station-fields” in an associative array in $extrafields. I have updated the code sample (not tested):

array(
    'pattern' => 'stations/(:any)',
    'method' => 'GET',
    'action' => function ($uid) {
        $db = new Database(array(
            'type'     => c::get('db.type'),
            'database' => c::get('db.database')
        ));
        $stations = $db->table('stations');
        $station = $stations->where('uid', '=', $uid)->first();

        if (!$station) {
            return site()->errorPage();
        } else {
            $extrafields = array(
                'station' => [
                    'uid' => $station->uid(),
                    'title'  => $station->title(),
                    'country' => $station->country(),
                    'region' => $station->region(),
                    'place'  => $station->place(),
                    'location'  => [
                        (float) $station->geolng(),
                        (float) $station->geolat()
                    ]
                ]
            );

            tpl::$data = array_merge(tpl::$data, array(
                'kirby' => kirby(),
                'site' => site(),
                'pages' => site()->children(),
                'page' => site()->visit('stations/station')
            ), $extrafields);

            // Load template with data and display it
            echo tpl::load(kirby()->roots()->templates().DS.'station.php', $extrafields);

            // Break route
            return false;
        }
    }
}

In the template you should now be able to use $station["title"]. It’s not quite the same as $page->title(); but in my case it works…

bvdputte · October 18, 2017, 9:43pm

I’m not sure. Maybe @texnixe knows this?

Another quick thought; if you really insist on using e.g. $page->title();, you could try setting up a model for the station template that re-wires these methods and get the values from the $station associative array?

paulrobertlloyd · October 18, 2017, 9:59pm

I thought about just that, but in this particular case, how would I access $station? Models only support $page (with $this) as far as I understand, and $station exists outside of $page. I’m not quite sure of the order of things, and where/when a page model gets called, and it’s relation to the router.

Using the $station["field"] format works for me to an extent, but becomes a pain when other components I’m using expect $page->field(). For some components I can pass the new value, but for others, doing this soon becomes overly hacky! The cleanest way (if perhaps dishonest from an underlying Kirby point of view), would be to disguise parts of my database as pages, as this is what the system often expects.

Hmm, tricky! Thanks so much for your help so far!

texnixe · October 19, 2017, 6:06am

I don’t think you can overwrite page data like this.

What could work, however, is something @jenstornell does in his reveal plugin. Instead of simply loading a template, you would then manipulate the data and render the preview with this data.

bvdputte · October 19, 2017, 8:26am

It is a hacky, messy workaround.

Hmm, I’ve quickly did a test and apparently the model is indeed loaded first, so it is unaware of $extrafields

I’m afraid there isn’t a nice way of working around the file system and getting a full-blown $page object from a database row.
I do think there will be options in Kirby Next (I’ve pm-ed you in the slack channel).

jenstornell · October 19, 2017, 9:09am

Hopefully things like this will get better in a later version of Kirby, but for now it’s quite tricky.

Use site()->visit() whenever possible, because else the $page object will be lost. You could send it as an argument with tpl::load, but if you use a snippet inside it, it will be lost again inside that snippet, in case you don’t sent it to the snippet as well.

Anyway, you can read more about page value manipulation here: https://github.com/jenstornell/kirby-secrets/blob/master/docs/page.md#page-value-manipulation

texnixe · October 19, 2017, 9:19am

See also this post for making the example “secrets solution” more dynamic: What’s the opposite of $page->toArray?

Topic		Replies	Views
Pass data from Router to Page Model Questions 🤔	3	871	February 4, 2016
Send data from route to model or controller Solved ✅	2	917	February 25, 2016
Cannot get Page Model to work Question❓	7	49	March 21, 2024
Virtual page - merge from page data Solved ✅	4	360	January 25, 2023
Kirby 3 + mysql database - Creating pages from a database Solved ✅	4	858	April 30, 2019

Create page model using information from database

Related Topics