Creating a select drop-down TV is not difficult, but suppose you want to exclude certain items, and want the first item to be blank? Here's an example of a complex query for the TV's @SELECT binding.
neiltipton asked...
I'm creating a tv which will allow me to select from a listbox any of the pages on my site that use a specific template. I can quite successfully create my list using the following Input Option Values:
@SELECT pagetitle, id FROM modx_site_content WHERE template = 17 ORDER BY pagetitle ASC
It would be useful to have a first item in the list which was blank, rather than it always being the first actual page alphabetically.
Can I exclude any weblinks that use the same template?
The Solution:
gissirob says...
This should work...
@select "-Make selection-", "" FROM DUAL UNION (SELECT pagetitle, id FROM modx_site_content WHERE template = 17 AND id NOT IN (SELECT id FROM modx_site_content WHERE class_key = 'modWebLink' AND template = 17) ORDER BY pagetitle ASC )
What's the Story?
"DUAL" acts as a dummy table name when no table is actually queried, in this case the string "-Make selection-", which will be the only row returned by the first SELECT query. The UNION statement joins SELECT query results into a single result set.
In this case, we have two separate SELECT statements, the first returning the string and the second returning the results of a nested SELECT query. The outer SELECT query gets all of the resources using the Template #17, then using NOT IN filters out the resources returned from the inner SELECT query - those that are weblinks.
