Converting IDs stored in a TV using SuperSelectBox to links

Support Extras
1

I have a template that uses SuperSelectBox to store IDs to other resources in three TVs. I was using getResources/pdoResources to query through the IDs for each TV and produce comma separated links. This resulted in very slow load times for the page (7-10seconds) listing all the resources using the template.

I implemented a funky work-around that greatly increased the speed of the page load (3-5seconds). I queried through the IDs in each TV using toLinks from tagLister.

See below:

Query the TV:

[[!toLinks? &items=[[+treaty-org-tribes]] &tpl=treaty-ids-to-links-tpl ]]

Using the tpl, Treaty-ids-to-links-ppl:

`<a href="[[~[[+item]]]]" class="[[+cls]]">[[pdoField?&id=`[[+item]]`]]</a>`

While this greatly improved the page load speeds, it is NOT the correct solution.

Is there a snippet or some php code to accomplish the process of taking a comma delineated list of IDs and output links using a tpl?

Thanks,
Roy
View the page at Treaties - Tribal Treaties Database (edited)

2

Here is some code that may work for your needs:

Snippet idsToLinks:

<?php
$ids = array_map('trim', explode('||', $ids));

$q = $this->xpdo->newQuery('modResource');
$q->where(array(
    'id:IN' => $ids
));
$q->sortby('FIELD(id, ' . implode(',',$ids) . ')','ASC');

$output = '';
$resources = $modx->getCollection('modResource', $q);
foreach($resources as $resource){
    $fields = $resource->toArray();
    $fields['url'] = $modx->makeUrl($fields['id'], '', '', -1);
    $output .= $modx->getChunk($tpl, $fields);
}
return $output;

Example call (from template):

[[idsToLinks? &ids=`3||4||5` &tpl=`tplIdsToLinks`]]
//assuming [[+treaty-org-tribes]] is a list of ids separated by '||'
[[idsToLinks? &ids=`[[+treaty-org-tribes]]` &tpl=`tplIdsToLinks`]]

Chunk tplIdsToLinks: (All the resource fields + url are available as placeholders)

<a href="[[+url]]">[[+pagetitle]]</a>
  • This code could probably still be improved in terms of speed if that is necessary.
  • I believe the SuperBoxSelect values are delimited by ||. If your ids are indeed separated by commas, then change the first line in the snippet code.
3

halftrainedharry,

thanks for the code. It works and gained some speed on the dev server. Page load down from an average of 5 seconds to 4.6 seconds. My long term goal is to have the content stored in JSON. Footable (data grid/table JS used on the site) loads the JSON faster and will reduce the load on the server. But currently the site is in Beta. Content is being updated/added 24/7 requiring dynamic querying.

4

There are some changes that you could make to my snippet code to improve the speed slightly.

E.g. only select the columns you really need ($q->select(array('id','pagetitle'));) or concatenate the link markup in the code without using a chunk.

<?php
$ids = array_map('trim', explode('||', $ids));

$q = $this->xpdo->newQuery('modResource');
$q->select(array('id','pagetitle')); //<-- NEW: only query columns id & pagetitle
$q->where(array(
    'id:IN' => $ids
));
$q->sortby('FIELD(id, ' . implode(',',$ids) . ')','ASC');

$output = '';
$resources = $modx->getCollection('modResource', $q);
foreach($resources as $resource){
    $fields = $resource->toArray('',false,true); //<-- CHANGED
    $fields['url'] = $modx->makeUrl($fields['id'], '', '', -1);
    $output .= '<a href="' . $fields['url'] . '">' . $fields['pagetitle'] . '</a>';  //<-- no longer using a chunk
}
return $output;

Maybe even use PDO instead:

<?php
$ids = array_map('trim', explode('||', $ids));

$q = $this->xpdo->newQuery('modResource');
$q->select(array('id','pagetitle'));
$q->where(array(
    'id:IN' => $ids
));
$q->sortby('FIELD(id, ' . implode(',',$ids) . ')','ASC');

$output = '';
if ($q->prepare() && $q->stmt->execute()) {
    $resources = $q->stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach($resources as $resource){
        $url = $modx->makeUrl($resource['id'], '', '', -1);
        $output .= '<a href="' . $url . '">' . $resource['pagetitle'] . '</a>'; 
    }
}
return $output;

But looking at your page, you probably need a completely different approach to make it fast.

  • It looks like you read all 39 pages at once and then do the paging client-side. Maybe you can do the paging server-side (with getPage or pdoPage) so you don’t have to load all the data in one request.
  • As you use the same data multiple times, it is probably much faster to load all the states and all the tribes once, create all the links once and then keep them in memory (or maybe even the cache for later requests) while you output all the data.
5

@halftrainedharry

I tested the pro version and gained another second. We have a default start up cloud on MODXCloud with all the typical snippets, templates, etc installed. I am adding your snippet to that cloud. Thank you, this will be a wonderful addition that simplifies using superselectbox.

However I am converting the page over to a static resource (that is updated regularly) to greatly speed up the load times see: Test Table - Tribal Treaties Database

Thanks for your help. Greatly appreciated.

Roy