Querying repeater fields directly using mysql.

[millipedia]

So.

I have a lovely map (really must write up that site one of these days), that pulls address data for each marker from a repeater field. Each shop can have multiple addresses with a lat lng and a few other fields.

The marker data is cached as geojson so it doesn't get generated each visit, and I refresh that cache every so often when new locations have been added.
That's all good, but rebuilding the geojson is beginning to take too long. At the moment I have a page selector for all the shops which then loops through all the addresses and then pulls out the location.

I thought I'd try just building a query in SQL to see if I could pull out the locations directly, but I'm struggling to get the relation of the address repeater page to the shop efficiently. The relation seems to be that the parent of the address repeater has the name 'for-page-' + the page id of the shop page.

The kind of thing I'm after in psuedocode is:

Select all the adresses (template 'repeater_addresses')
Left join those addresses on their parents ( address.parent_id = pages.id)
And then join those to the relevant shop page ( 'for-page-' + shop.id = address_parent.name )

reading that makes me think it'll have to be more that one query (and maybe I have it backwards and need to start with shops), but hey, I thought I'd see if anyone smarter than me had done this already or had any good suggestions

In PageFinder.php Ryan does something fancy with names and aliases but I haven't really got a handle on what he's doing.

This is really not an important thing at all, but I'm always keen to learn new ways to break things.

s.

[Robin S]

$pages->findRaw() will be helpful here. It's hugely faster than $pages->find() and easier to work with than SQL queries.

You could do a couple of searches and connect the resulting data by repeater page ID. Example:

// Ensure PagePaths module is installed so that URL is available to findRaw()
$shop_data = $pages->findRaw("template=shop", ['title', 'url', 'locations'], ['nulls' => true, 'flat' => true]);

// Here you could also get other fields from the repeater pages as needed
$location_data = $pages->findRaw("template=repeater_locations, check_access=0", ['location'], ['nulls' => true, 'flat' => true]);

$shops = [];
// Loop over the shop data and get the lat/lng for each location, matching by repeater page ID
foreach($shop_data as $id => $item) {
	$data = [
		'title' => $item['title'],
		'url' => $item['url'],
		'locations' => [],
	];
	$location_ids = explode(',', $item['locations.data']);
	foreach($location_ids as $location_id) {
		if(!isset($location_data[$location_id])) continue;
		$data['locations'][] = [
			'lat' => $location_data[$location_id]['location.lat'],
			'lng' => $location_data[$location_id]['location.lng'],
		];
	}
	$shops[$id] = $data;
}

db($shops);

 

[millipedia]

@Robin S - you are great. That's fantasticially helpful.

I don't think I've used findRaw before but it worked a treat.

According to Profiler rebuilding the markers has gone from 31 seconds to 0.3 secs:

It took me a couple of goes to convince myself it was actually rebuilding the markers and not pulling from cache - but it is!