Using SELECT field values from a dotCMS Structure to build a client side SELECT

Update. 6/02/2011 1:51:57 PM. Added detailed explanation of the code sample.

Sometimes you create a Structure with a SELECT or MULTISELECT whose value/label pairs you want to use on the front end too. For example, let's say I have a Structure called Book with a SELECT called "medium" that has the following values (CODE):

Hardback|0
Softcover|1
Audio|2
eBook|3

Now I want to build a book search page with medium as a criteria (as a SELECT). I could write the SELECT with hard-coded values, but what if I ever need to add new medium options to the Book Structure? I would like my search page to update itself without me having to do it - so instead I will create the SELECT with options drawn from the values stored in the Book#medium field.

There is no Lucene query that I know of that will query field values - but the SQL to do so is easy. First, you need to know the inode for the field. Use SQL such as below to find the inode.

select inode, structure_inode, field_name
from field
where field_name like '%medium%';

1. Use like '%medium%' if you are not entirely sure of what the field is called (percentages are wild cards in SQL). If you are sure, use = 'medium' instead.
2. Remember that SQL is case sensitive.
3. Remember that field names do not have to be unique across the entire database, so the above query might give you multiple results. That's why you select the other values as well - to help you identify which result is the one you actually want.

Once you have that, the Velocity code to build a SELECT from the field values is easy.

#getSQLResults('select field_values from field where inode = 49640')
#if($UtilMethods.isSet($SQLError))
   <p>Unable to load values. Please report this to the site administrator.</p>
#else
   <select>
      #set($labelsValuesField = $results.get(0).field_values)
      #set($labelsValuesArray = $labelsValuesField.split("\r"))
      #foreach ($labelValue in $labelsValuesArray)
         #set($labelValueArray = $labelValue.split("[\\|]"))
         #set($label = $listTool.get($labelValueArray, 0).trim())
         #set($value = $listTool.get($labelValueArray, 1).trim())
         <option value="$value">$label</option>
      #end
   </select>
#end

Some explanation for the above code.

#getSQLResults('select field_values from field where inode = 49640')
#if($UtilMethods.isSet($SQLError))
   <p>Unable to load values. Please report this to the site administrator.</p>
#else

Run SQL to retrieve the text used to generate the SELECT OPTION tags (on the back-end and now in our front-end). If the SQLError variable is set, something went wrong and there is no use trying anything further. Warn the user appropriately.

   <select>
      #set($labelsValuesField = $results.get(0).field_values)

The SQL we ran will only ever return 1 result, which will occupy the first position in the results object.

      #set($labelsValuesArray = $labelsValuesField.split("\r"))

The text used to generate the SELECT OPTION tags is first separated by newline characters (\r). Each line contains the label and value for one OPTION. Thus, we create an array where each cell in the array stores a single line.

      #foreach ($labelValue in $labelsValuesArray)

Go through each entry in the array we just created i.e. iterate over each line.

         #set($labelValueArray = $labelValue.split("[\\|]"))

Each line has two values - the first being for the OPTION label and the second being for the OPTION value. They are separated by the pipe character (|). Create another array where the first cell is the label and the second cell is the value.

         #set($label = $listTool.get($labelValueArray, 0).trim())

Get the contents for the first cell - the label.

         #set($value = $listTool.get($labelValueArray, 1).trim())

Get the contents of the second cell - the value.

         <option value="$value">$label</option>

Put the label and value together to create an OPTION tag within the SELECT.

      #end
   </select>
#end