select

This object generates an SQL-select statement to select records from the database.

Some records are hidden or timed by start- and end-times. This is automatically added to the SQL-select by looking for "enablefields" in the $GLOBALS['TCA'].

Warning

Do not use GET or POST data like GPvar directly with this object! Avoid SQL injections! Don't trust any external data! Secure any unknown data, for example with intval.

Properties

uidInList

uidInList
Data type

list of record_ids / stdWrap

Comma-separated list of record uids from the according database table. For example when the select function works on the table tt_content, then this will be uids of tt_content records.

Note: this is a special keyword and replaced with the id of the current record.

Attention

pidInList defaults to this. Therefore by default only records from the current page are available for uidInList. If records should be fetched globally, pidInList = 0 should also be set.

Example

EXT:site_package/Configuration/TypoScript/setup.typoscript
select {
   uidInList = 1,2,3
   pidInList = 0
}

select.uidInList = this

pidInList

pidInList
Data type

list of page_ids / stdWrap

Default

this

Comma-separated list of pids of the record. This will be page uids (pids). For example when the select function works on the table tt_content, then this will be pids of tt_content records, the parent pages of these records.

Pages in the list, which are not visible for the website user, are automatically removed from the list. Thereby no records from hidden, timed or access-protected pages will be selected! Nor will be records from recyclers. Exception: The hidden pages will be listed in preview mode.

Special keyword: this

Is replaced with the id of the current page.

Special keyword: root

Allows to select records from the root-page level (records with pid=0, e.g. useful for the table "sys_category" and others).

Special value: -1

Allows to select versioned records in workspaces directly.

Special value: 0

Allows to disable the pid constraint completely. Requirements: uidInList must be set or the table must have the prefix "static_*".

Note

Check the doktype of your backend page. If you are trying to fetch records from a sys_folder for example, the $cObj->checkPid_badDoktypeList method will insert the following SQL into your query:

[...]WHERE (`your_requested_table_name`.`uid` = 0) AND [...]

Which might result in an empty query result, depending on your records.

Example

Fetch related sys_category records stored in the MM intermediate table:

EXT:site_package/Configuration/TypoScript/setup.typoscript
10 = CONTENT
10 {
   table = sys_category
   select {
      pidInList = root,-1
      selectFields = sys_category.*
      join = sys_category_record_mm ON sys_category_record_mm.uid_local = sys_category.uid
      where.data = field:_ORIG_uid // field:uid
      where.intval = 1
      where.wrap = sys_category_record_mm.uid_foreign=|
      orderBy = sys_category_record_mm.sorting_foreign
      languageField = 0 # disable translation handling of sys_category
   }
}

recursive

recursive
Data type

integer / stdWrap

Default

0

Number of recursive levels for the pidInList.

orderBy

orderBy
Data type

SQL-orderBy / stdWrap

ORDER BY clause without the words "ORDER BY".

Example

EXT:site_package/Configuration/TypoScript/setup.typoscript
orderBy = sorting, title

groupBy

groupBy
Data type

SQL-groupBy / stdWrap

GROUP BY clause without the words "GROUP BY".

Example

EXT:site_package/Configuration/TypoScript/setup.typoscript
groupBy = CType

max

max
Data type

integer + Calc +"total" / stdWrap

Max records

Special keyword: "total" is substituted with count(*).

begin

begin
Data type

integer + Calc +"total" / stdWrap

Begin with record number value.

Special keyword: total

Is substituted with count(*).

where

where
Data type

SQL-where / stdWrap

WHERE clause without the word "WHERE".

Example

EXT:site_package/Configuration/TypoScript/setup.typoscript
where = (title LIKE '%SOMETHING%' AND NOT doktype)

Use {#fieldname} to make the database framework quote these fields:

EXT:site_package/Configuration/TypoScript/setup.typoscript
where = ({#title} LIKE {#%SOMETHING%} AND NOT {#doktype})

languageField

languageField
Data type

string / stdWrap

This defaults to whatever is defined in TCA "ctrl"-section in the "languageField". Change it to overwrite the behaviour in your query.

By default all records that have language-relevant information in the TCA "ctrl"-section are translated on translated pages.

This behaviour can be disabled by setting languageField = 0.

includeRecordsWithoutDefaultTranslation

includeRecordsWithoutDefaultTranslation
Data type

boolean / stdWrap

Default

0

If content language overlay is activated and the option languageField is not disabled, includeRecordsWithoutDefaultTranslation allows to additionally fetch records, which do not have a parent in the default language.

selectFields

selectFields
Data type

string / stdWrap

Default

*

List of fields to select, or count(*).

If the records need to be localized, please include the relevant localization-fields (uid, pid, languageField and transOrigPointerField). Otherwise the TYPO3 internal localization will not succeed.

join, leftjoin, rightjoin

join, leftjoin, rightjoin
Data type

string / stdWrap

Enter the JOIN clause without JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN respectively.

Example

Fetch related sys_category records stored in the MM intermediate table:

EXT:site_package/Configuration/TypoScript/setup.typoscript
10 = CONTENT
10 {
   table = sys_category
   select {
      pidInList = root,-1
      selectFields = sys_category.*
      join = sys_category_record_mm mm ON mm.uid_local = sys_category.uid
      # ....
    }
}

See pidInList for more examples.

markers

markers
Data type

(array of markers)

The markers defined in this section can be used, wrapped in the usual ###markername### way, in any other property of select. Each value is properly escaped and quoted to prevent SQL injection problems. This provides a way to safely use external data (e.g. database fields, GET/POST parameters) in a query.

Available sub-properties:

<markername>.value (value)

Sets the value directly.

<markername>.commaSeparatedList (boolean)

If set, the value is interpreted as a comma-separated list of values. Each value in the list is individually escaped and quoted.

(stdWrap properties ...)

All stdWrap properties can be used for each markername.

Warning

Since TYPO3 v8 there is a problem combining orderBy with markers caused by the quoting of the fields, see forge#87799.

Example

EXT:site_package/Configuration/TypoScript/setup.typoscript
page.60 = CONTENT
page.60 {
    table = tt_content
    select {
        pidInList = 73
        where = header != ###whatever###
        markers {
            whatever.data = GP:first
        }
    }
}

This example selects all records from table tt_content, which are on page 73 and which don't have the header set to the value provided by the Get/Post variable "first".

EXT:site_package/Configuration/TypoScript/setup.typoscript
page.60 = CONTENT
page.60 {
    table = tt_content
    select {
        pidInList = 73
        where = header != ###whatever###
        markers {
            whatever.value = some
            whatever.wrap = |thing
        }
    }
}

This examples selects all records from the table tt_content which are on page 73 and which don't have a header set to a value constructed by whatever.value and whatever.wrap ('something').

Quoting of fields

It is possible to use {#fieldname} to make the database framework quote these fields (see Important: #80506 - Dbal compatible field quoting in TypoScript):

EXT:site_package/Configuration/TypoScript/setup.typoscript
select.where = ({#title} LIKE {#%SOMETHING%} AND NOT {#doktype})

This applies to:

  • select.where

but not to:

  • select.groupBy

  • select.orderBy

as these parameters already follow a stricter syntax that allow automatic parsing and quoting.

Example

See PHP source code for \TYPO3\CMS\Frontend\ContentObject\ContentObjectRenderer, ContentObjectRenderer::getQuery(), ContentObjectRenderer::getWhere().

Condensed form:

EXT:site_package/Configuration/TypoScript/setup.typoscript
10 = CONTENT
10 {
   table =
   select {
      uidInList =
      pidInList =
      recursive =
      orderBy =
      groupBy =
      max =
      begin =
      where =
      languageField =
      includeRecordsWithoutDefaultTranslation =
      selectFields =
      join =
      leftjoin =
      rightjoin =
   }
}

See also:

  • CONTENT: for more complete examples with select and rendering the output with renderObj

  • wrap: enclosing results within text, used in some of the examples above

  • stdWrap: for more functionality, can be used in some of the properties, such as pidInList, selectFields etc.