Skip to content

Instantly share code, notes, and snippets.

@pepebe
Created July 13, 2012 16:27
Show Gist options
  • Save pepebe/3105800 to your computer and use it in GitHub Desktop.
Save pepebe/3105800 to your computer and use it in GitHub Desktop.
MODX REVO: Sorting a @SELECT-bound and UNION'ed ListBox TV
/* Source: http://forums.modx.com/index.php?topic=30102.0
Eequirements 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
*/
@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
@pepebe
Copy link
Author

pepebe commented Jul 13, 2012

Quote from: MIKE schell

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):
[list]
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
[/list]

After monkeying around with sql for a few minutes, I ended up with the following code in the 'Input Option Values' box.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment