foreign_table_where¶
- foreign_table_where¶
- Path
$GLOBALS['TCA'][$table]['columns'][$field]['config']
- 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 ORDER BY
and GROUP BY
should NOT be quoted, since they always receive proper quoting automatically
through the API.
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.
So for example
sys_language_uid
must be referenced using###REC_FIELD_sys_language_uid###
- ###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.
- ###SITE:<KEY>.<SUBKEY>###
A value from the site configuration, for example:
###SITE:mySetting.categoryPid###
or###SITE:rootPageId###
.
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¶

[
'columns' => [
'select_single_3' => [
'label' => 'select_single_3 static values, dividers, foreign_table_where',
'config' => [
'type' => 'select',
'renderType' => 'selectSingle',
'items' => [
[
'label' => 'Static values',
'value' => '--div--',
],
[
'label' => 'static -2',
'value' => -2,
],
[
'label' => 'static -1',
'value' => -1,
],
[
'label' => 'DB values',
'value' => '--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: ',
],
],
],
]
Tree field with foreign_table_where¶

[
'columns' => [
'select_tree_6' => [
'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,
],
],
],
],
],
]