modx: rewriting a getResources query in a snippet

Question

I am fairly new to MODX and have been working on interfaces to talk to MODX. Now I got the following query:

[[!getCache?
    &element=`getResources`
    &cacheExpires=`9000`
    &parents=`679,663,641,799,590`
    &depth=`0`
    &tpl=`related-article-in-page-listing`
    &limit=`6`
    &includeTVs=`0`
    &includeContent=`0`
    &showHidden=`1`
    &tvFilters=`Related Artist==[[*Related Artist]]`
    &toPlaceholder=`new`
    &resources=`-[[*id]]`
]]

that I would like to translate to a snippet so I can call it with the API via the runSnippet() method.

I've gotten this far:

<?php
$output = '';
$id = !empty($id) ? $id : 0;

$modx= new modX();
$modx->initialize('mgr');

$query = $modx->newQuery('modResource');
$query->where(array(
   'parent' => 679,
   'published' => 1,
   'Related Artists.value:=' => 603 // this will be replaced with $id when I get it to work...
));
$query->limit(5);
$titles = $modx->getCollection('modResource',$query);


foreach ($titles as $k) {
    $output.= '<li>'.$k->get('pagetitle').'</li>'; // let's just get the titles first
}

return '<ul>'.$output.'</ul>';

this will not return anything and I think I am just not clear as to what the above query actually calls in MySQL terms. As var as I could tell it looks for entries that have either 679,663,641,799 or 590 as parent and the TV [[*Related Artist]] looks into a flied presumably called "Related Artist" for an exact match?!

Any help is appreciated here as I have been wrangling with the Book and online help for a couple days now ;) Thanks

Answer 1

I got it:

$query = $modx->newQuery('modResource');
$query->leftJoin('modTemplateVarResource', 'tv_value', 'tv_value.contentid = modResource.id');
$query->where(array(
    'modResource.parent:IN' => array(663,679,641,799,590),
    'modResource.published' => 1,
    'tv_value.tmplvarid' => 13,
    'tv_value.value' => $ID // this has been replaced with a dynamic $id but was "603" in my question
));
$query->limit(10);
$titles = $modx->getCollection('modResource',$query);

(13 is the ID of my TV)

So I now understand how the joins work in xPDO ;) I hope this helps someone else some day