How to count number of resources with date TV is-not-empty in a parent resource?

Case: I need to count the number of resources (or number of TVs) within a parent resource ($parentID) with TV ($tvID) is-not-empty.

I have this code working

$total = $modx->getCount('modTemplateVarResource', array('tmplvarid' => $tvID, 'value' != $value));
return $total;

and the snippet call:

[[!tvValueCount? &value=`` &tvID=`1`]]

but it count all not-empty-tvs with that id in all resources, and I need to reduce it to, lets say, $parentID. So basicly I need a snippet call like this:

[[!tvValueCount? &value=`` &tvID=`xx` &parentID=`xx`]]

I expect it to be very simple, but I not a good enough programmer to get it done the best way according to database loads, prosessing time, server load and so on.

There will be around 3-4000 resources to count from at the most.

Solution

You could use something like this:

<?php
$c = $modx->newQuery('modTemplateVarResource');
$c->leftJoin('modResource', 'Resource', array(
    'Resource.id = modTemplateVarResource.contentid'
));
$c->where(array(
    'modTemplateVarResource.tmplvarid' => $tvID,
    'modTemplateVarResource.value:!=' => '',
    'Resource.parent' => $parentID,
));
return $modx->getCount('modTemplateVarResource', $c);
return $total;

Or join the tables from the other side:

<?php
$c = $modx->newQuery('modResource');
$c->leftJoin('modTemplateVarResource', 'TemplateVarResources', array(
    'Resource.id = modTemplateVarResource.contentid'
));
$c->where(array(
    'modTemplateVarResource.tmplvarid' => $tvID,
    'modTemplateVarResource.value:!=' => '',
    'Resource.parent' => $parentID,
));
return $modx->getCount('modResource', $c);
return $total;

This will count all Resources that have the parent $parentID (only one level), where the Template Variable is filled with a not empty string. It does not count Resources that use the default template variable value.

The code has to be extended to query deleted/unpublished Resources, to count Resources in more than one level etc.

But before you try to extend the code, you should better use a getResources or pdoResources snippet call. They are built for this and you don't have to hassle with the exeptions above. And they fill a placeholder with the count of the found Resources.

This should work with getResources:

[[getResources?
&parents=`parents`
&includeTVs=`tvName`
&tvFilters=`tvName!=`
&totalVar=`totalnotempty`
]]
[[+totalnotempty]]

parents and tvName have to be replaced with real values.