Funny thing while testing, came across an issue where my sub select was not accessible directly.
Example:
<?php
$graph = '{ "Ancestors": {}, "Descendants": {} }';
$c = $modx->newQuery('myTree');
// This subquery was not accessible in the loop
$c->select('( SELECT COUNT(*)
FROM '.$modx->getTableName('myTreeClosure').'
WHERE ancestor = myTree.id
AND descendant != myTree.id ) AS `nb_branches`');
$c->where(array(
'Descendants.ancestor' => 0,
'Descendants.depth' => 0,
'myTree.leaf' => $id,
));
$c->groupBy('myTree.title');
$branches = $modx->getCollectionGraph( 'myTree', $graph, $c );
foreach ($branches as $branch) {
//That was not working even though the SQL dump was working correctly
$nb = $branch->get('nb_branches');
}
However, by change the alias of the filed it was then accessible like i wanted it to:
<?php
// Give a used prefix to the alias of the retreived field - here i use myTree
$c->select('( SELECT COUNT(*)
FROM '.$modx->getTableName('myTreeClosure').'
WHERE ancestor = myTree.id
AND descendant != myTree.id ) AS `myTree_nb_branches`');
//Then the loop was working
foreach ($branches as $branch) {
$nb = $branch->get('nb_branches');
//or
$array[] = $branch->toArray();
}
It works as i want but is there any more cleaner method to achieve the same result ?