This may be a bit obscure, but it also might help someone out, so here goes
I had the following requirements for a new Template Variable (TV):
- select box
- filled with a list of documents from a certain modx container
- also wanted @INHERIT to appear at end of select box list
- wanted documents sorted according to their menuindex
After monkeying around with sql for a few minutes, I ended up with the following code in the ’Input Option Values’ box.
@SELECT pagetitle, id FROM ( (SELECT pagetitle, id, menuindex FROM [+PREFIX+]site_content WHERE parent = 21) UNION (SELECT '@INHERIT', '@INHERIT', 9999) ORDER BY menuindex) as sc
The trick was that you can’t sort a UNION unless the things you’re unioning are both surrounded in parentheses, and with modx’s @SELECT binding, you don’t have the opportunity to surround the first SELECT because it’s created automatically. The solution was to do all of that as a subquery.
Don’t try this if your MySQL version is < 4.1
MODX Staff