Getting all descendants of a document using MySQL

This MySQL query isn’t easy... how can you list ALL descendants of one page? It’s easy to list just the children, but if the tree goes to arbitrary depths, it gets weird. I need a solution that doesn’t require multiple queries or dynamic generation of the query text.... I don’t think such a query is possible though...

This is the easy query (selects Children, i.e. tier 1):


And I can get Children and Grandchildren using something like this (tiers 1 and 2):


And then 3 tiers:


I think I just have limit the number of tiers my application can support. The overhead for infinite depth is probably out of the project scope.

Unless someone knows of a way I can pull this off with MySQL so I can use it in a Snippet?

Thanks!

why not dynamic generation??

Hi,

you should have a look at ditto class.
The best way to get all descendants children without executing a sql query is to use the $modx->documentMap.

:-)

The best way to get all descendants children without executing a sql query is to use the $modx->documentMap.

Now there is a well kept secret. $modx->documentMap is not in any of the api documentation that I could find. What is the syntax for using it?

Thanks for the input.

To quote my original spec:

I need a solution that doesn’t require multiple queries or dynamic generation of the query text

In other words, the implementation of this cannot involve PHP manipulation of the query string. This is because the query needs to be used in @SELECT bindings and on the eForm #SELECT parameters. The workaround is to use dynamic generation of a list, then validate against that list instead of using the query. Maybe I can use $modx->documentMap to generate a list that I can validate against...

I’m still curious about this elusive query, though...

You can get all children with a single query. But to get all grand children and beyond you will need more complex or multiple queries.

The function $modx->getChildIds() is what you want to use to get the id’s of children documents without issuing any queries at all. It uses the $modx->documentMap to retrieve the child id’s of a particular document and provides parameters to get grandchildren as well (depth), though you may want to get one level at a time so you know where they are...

The function $modx->getChildIds() is what you want to use

That is what I thought

Thanks. I think I can work that in nicely.