xPDO get resource with specific TV value (which was not deleted)

[vibedesign]

I need to check if there is a resource that holds a specific value in a specific TV. I found the basics in this old forum post. Now I struggle on how I can add that it doesn’t return deleted resources. Here’s what I have so far:

$tvValue = 'XYZ';

$query = $modx->newQuery('modResource');
$query->leftjoin('modTemplateVarResource','TV','tmplvarid=6 AND contentid=modResource.id');
$query->where(array('TV.value'=>$tvValue));
  
$resource = $modx->getObject('modResource', $query);

if (!$resource) {
  // do things
}

I’m guessing that this can also be simplified to return the modResource object directly instead of the ID and then retrieving the object from that ID again. My goal would be to check if there is such a resource and execute code accordingly.

Also is there a way to filter by TV name instead of the TVs ID (... tmplvarid=6 ...)? Not sure where to look for possible parameters here, still learning a lot about xPDO.

[bobray]

I think it might be faster to get the modTemplateVarResource first. This assumes that none of them are set to a default value. In that case there’s no record in the table. It also assumes that there will only be one record with that value (which your code implies).

/* untested */
$tvValue = 'xyz';

$c = array('tmplvarid' => 6, 'value'=>$tvValue);

$tv = $modx->getObject('modTemplateVarResource', $c);

if ($tv) {
    $resourceObject =   $tv->getOne('Resource');
} else {
   /* No Resource */
}

It’s possible to do this with the name of the TV, but it would slow things down because the TV name is in a different table.

[bruno17]

to get only undeleted resources:

$query->where(array('modResource.deleted'=>'0','TV.value'=>$tvValue));

if you want to use the tvname, you need another join to modTemplateVar

[bruno17]

Keep in mind, there could also be a default-value or bindings and processed values.
If this needs to be respected, it gets more complicated.

pdoTools could help, to build the filter query.