foreign_table_where

foreign_table_where
Type:string (SQL WHERE)
Scope:Proc. / Display
RenderType:all

The items from foreign_table are selected with this WHERE-clause.

Field quoting

The example below uses the special field quoting syntax {#...} around identifiers of the QueryHelper to be as DBAL compatible as possible. Note that the ORDER BY is NOT quoted since that is parsed out anyway and always receives proper quoting.

Markers inside the WHERE statement

It is possible to use markers in the WHERE clause:

###REC_FIELD_[field name]###

Any field of the current record.

Note

The field name part of the marker is not in upper case letters. It must match the exact case used in the database.

###THIS_UID###
Current element uid (zero if new).
###CURRENT_PID###
The current page id (pid of the record).

###SITEROOT###

###PAGE_TSCONFIG_ID###
A value you can set from Page TSconfig dynamically.
###PAGE_TSCONFIG_IDLIST###
A value you can set from Page TSconfig dynamically.
###PAGE_TSCONFIG_STR###
A value you can set from Page TSconfig dynamically.

The markers are preprocessed so that the value of CURRENT_PID and PAGE_TSCONFIG_ID are always integers (default is zero), PAGE_TSCONFIG_IDLIST will always be a comma-separated list of integers (default is zero) and PAGE_TSCONFIG_STR will be addslashes’ed before substitution (default is blank string).

More information about markers set by Page TSconfig can be found in the TSconfig reference.

Examples

Select single field with foreign_prefix and foreign_where

      'select_single_3' => [ 
         'exclude' => 1,
         'label' => 'select_single_3 static values, dividers, foreign_table_where',
         'config' => [ 
            'type' => 'select',
            'renderType' => 'selectSingle',
            'items' => [ 
               '0' => [ 
                  '0' => 'Static values',
                  '1' => '--div--',
               ],
               '1' => [ 
                  '0' => 'static -2',
                  '1' => -2,
               ],
               '2' => [ 
                  '0' => 'static -1',
                  '1' => -1,
               ],
               '3' => [ 
                  '0' => 'DB values',
                  '1' => '--div--',
               ],
            ],
            'foreign_table' => 'tx_styleguide_staticdata',
            'foreign_table_where' => 'AND {#tx_styleguide_staticdata}.{#value_1} LIKE \'%foo%\' ORDER BY uid',
            'foreign_table_prefix' => 'A prefix: ',
         ],
      ],
      // Example from extension "styleguide", table "tx_styleguide_elements_select"

Tree field with foreign_table_where

      'select_tree_6' => [ 
         'exclude' => 1,
         'label' => 'select_tree_6 categories',
         'config' => [ 
            'type' => 'select',
            'renderType' => 'selectTree',
            'foreign_table' => 'sys_category',
            'foreign_table_where' => 'AND ({#sys_category}.{#sys_language_uid} = 0 OR {#sys_category}.{#l10n_parent} = 0) ORDER BY sys_category.sorting',
            'size' => 20,
            'treeConfig' => [ 
               'parentField' => 'parent',
               'appearance' => [ 
                  'expandAll' => true,
                  'showHeader' => true,
               ],
            ],
         ],
      ],
      // Example from extension "styleguide", table "tx_styleguide_elements_select"