How would you get resources with xPDO based on the value of a TV?

46 Posts

zenpixel Reply #1, 10 years, 11 months ago

Is there a way to get a single resource with xPDO, where I only know the value of a template variable? Let's say the TV has an id=7 and I know that the resource I want has tv7='foo'

Or, for lack of a better illustration, something like this:

$page = $modx->getObject('modResource', array('tv7'=>'foo'));
$id = $page->get('id');

...Can something like this be done with getObject? Or, would I need to do some kind of join query in plain SQL?
Any ideas would be greatly appreciated.

This question has been answered by Bruno17. See the first response.

46 Posts

zenpixel Reply #2, 10 years, 11 months ago

This works but seems a little clunky:

// first get the ID of the page:
$the_id_i_want = $modx->query( "SELECT contentid FROM modx_site_tmplvar_contentvalues WHERE tmplvarid=7 AND value='foo'" );
// then get the page itself:
$row = $the_id_i_want->fetch(PDO::FETCH_ASSOC);
$page = $modx->getObject('modResource', $row['contentid']);

Does anyone know if this can be done in a single query?

discuss.answer

5,888 Posts

Bruno17 Reply #3, 10 years, 11 months ago

$c = $modx->newQuery('modResource');
$c->leftjoin('modTemplateVarResource','TV','tmplvarid=7 AND contentid=modResource.id');
$c->where(array('TV.value'=>'foo'));
//could maybe be more than one resource with that value
if ($collection = $modx->getCollection('modResource',$c)){
    foreach ($collection as $resource){
        //do something with the resource-object
        
    }
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #4, 10 years, 11 months ago

Be aware that searching and sorting by TVs can be tricky. Bruno17's excellent code, for example, won't retrieve any resources where the TV is set to its default value, because such TVs have no record in the modTemplateVarResource table. It also may have trouble with TVs that need processing (e.g., @INHERIT TVs), since TV.value will refer to the raw content of the TV.

Bruno17's code is perfect, however, if there is no default value for the TV(s) and you can use the raw value of each TV, which is often the case when you want to search by TV values.

There's a more detailed explanation of the issue and a suggested alternative here: http://bobsguides.com/blog.html/2014/05/27/why-extend-moduser/.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

46 Posts

zenpixel Reply #5, 10 years, 11 months ago

thanks @Bruno17 ! That is an awesome solution. Works perfectly.

@BobRay thanks for the excellent insight. In my case the default TV value is not an issue, but, I can see how that would be a concern in other situations.

Answered How would you get resources with xPDO based on the value of a TV?