ModX Revo: Query Multiple TVs?

Question

I'm moving a site to ModX Revolution and have no experience with xPDO.
The site I'm moving has a search feature that looks through a few TVs assigned to some resources and returns the applicable pages.
I'm having trouble incorporating this using xPDO.

I'm able to return all pages where a TV is set to a given value, but I can't figure out how to expand this into:
Find all resources where TV1 == X, TV2 == Y, TV3 == Z.
How can I query multiple TVs at once?

$value = "Mexico";
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVarResource','TemplateVarResources');
$c->where(array(
     'TemplateVarResources.tmplvarid' => 7,
     '"'.$value.'" IN (TemplateVarResources.value)',
));

$resources = $modx->getCollection('modResource',$c);

score 0 · Answer 1 · answered Aug 19 '13 at 20:15

0

Hmmmmm I think it goes something like this:

$c->where(array(
     'TV1:=' => 'X'
     'AND:TV2:=' => 'Y'
     'AND:TV3:=' => 'Z'
));

Would be good to check the docs here: http://rtfm.modx.com/xpdo/2.x/class-reference/xpdoquery

answered Aug 19 '13 at 20:15

Sean Kimball

4,506
9
42
73

Vasis · Answer 2 · 2013-08-19T21:01:22.273

Working example from my code for two tvs: reserved (id:11) and cost_obj (id:12)

$c->leftJoin('modTemplateVarResource', 'cost_obj', array('modResource.id = cost_obj.contentid', 'cost_obj.tmplvarid = 12'));
$c->leftJoin('modTemplateVarResource', 'reserved', array('modResource.id = reserved.contentid', 'reserved.tmplvarid = 11'));
$c->query['where'][0][0][] = new xPDOQueryCondition(array(
   'sql' => 'CAST(`reserved`.`value` as UNSIGNED INTEGER) > ?',
   'binding' => array(
                     'value' => 0,
                     'type' => PDO::PARAM_INT,
                     'length'=>0),
   'conjunction'=>'AND')
);
$c->query['where'][0][1][] = new xPDOQueryCondition(array(
   'sql' => 'CAST(`cost_obj`.`value` as UNSIGNED INTEGER) > ?',
   'binding' => array(
                     'value' => 0,
                     'type' => PDO::PARAM_INT,
                     'length'=>0),
   'conjunction'=>'AND')
);
$c->query['where'][0][2][] = new xPDOQueryCondition(array(
   'sql' => 'CAST(`reserved`.`value` as UNSIGNED INTEGER) >= CAST(`cost_obj`.`value` as UNSIGNED INTEGER)',
   'conjunction'=>'AND')
);

this query equal

reserved > 0 AND cost_obj > 0 AND reserved >= cost_obj

score 0 · Answer 3 · answered Aug 20 '13 at 20:09

Simply innerJoin a modTemplateVar table for each TV you want to search on. Note the third param in the join which contains the ON terms. Make sure you specify the correct TV id's you want to get.

$value = "Mexico";
$c = $modx->newQuery('modResource');
$c->innerJoin('modTemplateVar', 'TV1', 'TV1.contentid = modResource.id AND TV1.tmplvarid = X');
$c->innerJoin('modTemplateVar', 'TV2', 'TV2.contentid = modResource.id AND TV2.tmplvarid = Y');
$c->innerJoin('modTemplateVar', 'TV3', 'TV3.contentid = modResource.id AND TV3.tmplvarid = Z');
$c->where(array(
   'TV1.value:LIKE' => $value,
   'TV2.value'...,
   'TV3.value'...
));

$resources = $modx->getCollection('modResource',$c);

Please note that this is untested but it should give you the general idea of how you should set up your query. You may need to add some backticks to the table names in the ON clauses, dunno how picky the xPDO parser is about that.

Also note that this isn't a very effective query on larger sets. The most effective way of accomplishing this would be to make a custom xpdo object that suits your needs, but thats quite a project if you only need it for this specific case.

If possible you might also just try doing a getResources call, where it's fairly easy to add TV contitionals. It's still slow since it's done with more or less the same joins but atleast you won't have to troubleshoot your way to the solution.

[[getResources?
    &parents=`1,2,3...`
    &depth=`10` 
    &tpl=`yourTemplateForEachResource`
    &tvFilters=`nameOfTV1==%[[+value]]%,othertv==othervalue`
]]

Note that comma is AND's and double pipes are OR's in the tvFilters string. And doing it programatically:

$value = 'Mexico';
$resources = $modx->runSnippet('getResources', array(
    'parents' => '1,2,3...',
    'depth' => 10,
    'tpl' => 'yourTemplateForEachResource',
    'tvFilters' => 'nameOfTV1==%' . $value . '%,othertv==othervalue'
));

I guess building your own query would usually be faster but there's some caching and optimizing built into getResources that might win in the long run (if used correctly).

ModX Revo: Query Multiple TVs?

3 Answers3