Querying for a list of resources by the value of a template variable

43 Posts

jshethnyc Reply #1, 16 years, 2 months ago

Hi,

for our Modx Revolution-powered site, we have a radio template variable called "featured" that’s set to either 1, or 0.

We have code that pulls a single featured resource document that could be contained in one of several resource folders by using an SQL query:

$featuredDocQuery = "SELECT * FROM modx_site_content WHERE id IN (
SELECT tv.contentid FROM `modx_site_tmplvar_contentvalues` tv
WHERE tmplvarid = ( SELECT id FROM `modx_site_tmplvars` WHERE name = 'featured' )
)
AND parent IN (10,21,37,45,52) LIMIT 1";

$result = $modx->query($featuredDocQuery);

foreach($result as $document){
    $featuredArticle = $document;
    break;
}
//Use data from $result which is an associative array

I was wondering how to do this using xPDO’s getCollection method.

Thanks in advance!

Regards,
- Jay

4,149 Posts

splittingred Reply #2, 16 years, 2 months ago

This should work:

$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->innerJoin('modTemplateVar','TemplateVar','`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`');
$c->where(array(
    'TemplateVar.name' => 'featured',
    'TemplateVarResources.value' => 1,
    'modResource.parent:IN' => array(10,21,37,45,52),
));
$resources = $modx->getCollection('modResource',$c);

foreach ($resources as $resource) {
   /* each $resource is a xPDOObject, so $resource->get('id') and such... */
}

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

43 Posts

jshethnyc Reply #3, 16 years, 2 months ago

Hi,

thanks so much for your reply and the code! I’ll give it a try.

On a side-note, I was looking at the getResources package ( http://svn.modxcms.com/docs/display/ADDON/getResources ). It seems to be the recommended way (over Ditto) to get aggregated data in Revolution.

I was trying something along these lines to get the same data:

Snippet call:

[[!getResources? &parents=`10,21,37,45,52` &limit=`1` &includeContent=`1` &includeTVs=`1` &tvFilters=`featured==1` &tpl=`featuredArticle`]]

And then the chunk called featuredArticle looks like this:

Testing: 1,2,3
[[+content]]

When I preview the resource that calls this snippet, I don’t get any output from the getResources snippet. I did install getResources through Package Management, but the getResources snippet does not seem to be getting called.

I know this is the xPDO forum, but rather than opening a new thread in a separate forum, I thought it made sense to add it here, since it would accomplish the same thing through a different (and simpler) method.

Any help would be appreciated.
Thanks in advance.

1,624 Posts

cipa Reply #4, 16 years, 2 months ago

Your getResources seems ok

Try removing the &limit and the &tvFilters params. If you get results than probably the &tvFilters is not correct.

Responsive CSS Grid: cipalabs.com/projects/metal/
Blog: MODX Tutorials & Articles