What if you have a value of one TV and you want to get the value of another TV for the same resource? Between Jason Coward (opengeek) and Donald Atkinson (fuzzicallogic) I got this snippet. The two TV names and the known TV value could even be made into snippet properties.
$tvAName = 'tvA'; $tvBName = 'tvB'; $tvAValue = 'valueA'; $query = $modx->newQuery('modResource'); $query->select('modResource.id'); $query->innerJoin('modTemplateVarResource', 'tvv', array( 'tvv.contentid = modResource.id' )); $query->innerJoin('modTemplateVar', 'tv', array('tv.id = tvv.tmplvarid')); $query->where(array( 'tv.name' => $tvAName )); $query->andCondition(array( 'tvv.value' => $tvAValue )); $productResource = $modx->getObject('modResource', $query); $tvBValue = $productResource->getTVValue($tvBName); return $tvBValue;
What's the Story
I had a list of items from a given user's Extended Fields. These were identifiers for various products, and the resources for these products had their identifiers in TVs. I needed to get at another TV value for the same resource, in order to display a nice list of items for the user.
What I had: TV A's name, TV B's name, and TV A's value.
What I needed: TV B's value for the same resource that had that value in TV A.
This code snippet starts out by creating a new Query object that will return a modResource object. In this case, I didn't want any other fields from the resource, so I told it to just SELECT the resource ID. If you will be wanting to use other resource fields, just remove that line and all of the resource's fields will be in the results. Then it uses some INNER JOINs to connect the TVs by the contentid field of the TV with the known value. Then it gets the modResource object with that contentid, and finally I can get that resource's TV B value.
Here's the actual query that this xPDO query object generates:
SELECT modResource.id FROM `modx_site_content` AS `modResource` JOIN `modx_site_tmplvar_contentvalues` `tvv` ON tvv.contentid = modResource.id JOIN `modx_site_tmplvars` `tv` ON tv.id = tvv.tmplvarid WHERE ( `tv`.`name` = 'tvA' AND `tvv`.`value` = 'valueA' )