We’ve got a Kirby site that integrates a SQLite database with addresses in the panel using virtual pages. The panel integration works well, everything is displayed as expected. But I noticed two things:
- The site index is slow.
- The global panel search is unresponsive when searching a page.
I noticed that the search requests were taking very, very long. After waiting for a while, finally there were results shown but I noticed that those included results from the database. I didn’t expect that.
So I have two questions:
- How can I make sure that Kirby doesn’t try to grab my entire database to create a page index?
- How can I make my virtual page searchable (which would be very helpful) without compromising performance?
Here are the models used to create the virtual pages:
places.php
<?php
class PlacesPage extends Page
{
public function children()
{
$places = [];
foreach (range('A', 'Z') as $letter) {
$places[] = [
'slug' => strtolower($letter),
'num' => 0,
'template' => 'streets',
'model' => 'streets',
'content' => [
'title' => 'Straßenindex ' . $letter
]
];
}
return Pages::factory($places, $this);
}
}
streets.php
<?php
class StreetsPage extends Page
{
public function children()
{
$database = new Database([
'type' => 'sqlite',
'database' => kirby()->root('site') . '/data/addresses.sqlite',
]);
$collection = $database->query(
'SELECT DISTINCT street FROM addresses
WHERE street LIKE :letter
LIMIT 500
',
[
':letter' => $this->slug() . '%',
]
);
$streets = [];
foreach ($collection->sortBy('street', 'asc') as $name) {
$streets[] = [
'slug' => Str::slug($name->street()),
'num' => 0,
'template' => 'street',
'model' => 'street',
'content' => [
'title' => $name->street(),
],
];
}
return Pages::factory($streets, $this);
}
}
street.php
<?php
class StreetPage extends Page
{
public function children()
{
$database = new Database([
'type' => 'sqlite',
'database' => kirby()->root('site') . '/data/addresses.sqlite'
]);
$collection = $database->query(
'SELECT * FROM addresses
WHERE street LIKE :street
LIMIT 500
',
[
':street' => $this->title()
]
);
$places = [];
foreach ($collection->sortBy('housenumber', 'asc') as $place) {
$places[] = [
'slug' => $place->id(),
'num' => 0,
'template' => 'place',
'model' => 'place',
'content' => [
'title' => $place->street() . ' ' . $place->housenumber(),
'street' => $place->street(),
'housenumber' => $place->housenumber(),
'postcode' => $place->postcode(),
'city' => $place->city(),
'name' => $place->name(),
'osm' => $place->id(),
'longitude' => $place->longitude(),
'latitude' => $place->latitude()
]
];
}
return Pages::factory($places, $this);
}
}
These are the search results that popped up after a while. Each result is a virtual page. I don’t know why those results are though, because they don’t correspond to the search term (which is actually a streetname available in the database):
