Template Variables

Complex Queries Using @SELECT Binding

light fare

@SELECT Binding

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
(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.

Legacy Multi-select List

New Multi-select List