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. The WHERE clause is effectively appended to the existing WHERE clause (which contains default constraints, such as NOT deleted) and must begin with :sql:`AND.

Field quoting

The example below uses the special field quoting syntax {#...} around identifiers 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 quoted 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

EXT:styleguide/Configuration/TCA/tx_styleguide_elements_select.php

[
    '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

EXT:styleguide/Configuration/TCA/tx_styleguide_elements_select.php

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