Find Records Without Administrative Database Access
Sometimes it’s necessary to find records on a low-level approach, whilst no administrative access to the underlying database is given. Luckily, TYPO3 got your back and assists you in accessing the database and lets you write database queries without the need of knowing the SQL syntax of the used database system.
Requirements
To be able to do so, some prerequisites must be fulfilled:
- The extension "lowlevel" must be installed (Composer package "typo3/cms-lowlevel")
- A backend user with administrative privileges is required to access the backend module
The Backend Module
EXT:lowlevel brings a dedicated backend module called "DB check" located in the left-hand module menu in the "System" section.
The module offers a couple of submodules for certain database related operations. For this article, "Search" is relevant.
Raw search in all fields
This search mode is used to get quick results matching a text that is searched for in all available tables as defined in the TCA. Since there is no further limitation configurable, it’s like hitting the database with a pickaxe. It’s advised to use this search mode only with very explicit terms to limit the results.
Advanced query
The “Advanced query” mode lets the user define a fine-grained query that can be saved and re-used at a later point.
To create a search, a database table must be selected at first by choosing an available option in “Select a table”. The list contains all tables defined in TCA, thus their according labels are presented in that list. After a table has been selected, the module instantly reloads and offers other options depending on the previous selection.
“Select fields” offers a list of fields that can be selected and will be rendered in the result set produced by the query.
Below, there is a section “Make query” which allows to add conditions to the query. This helps to limit the result set by asking for requirements to each result. For example, one might just want to search content elements that are either of type “Header” or “Text & Media”.
The conditional operator (e.g. “equals”, “is True”) that belongs to each condition is also negatable by clicking the checkbox next to the operator.
This setup now tells the database to show the uid and the header of each content element, whose header contains the word “flexible”, is of type “Text” and is visible*.
* The field “Visible” is internally named “hidden”, which might leads to confusion here. This is subject of change in further releases.
At the bottom, it’s possible to define a limitation of the result size, to group results by a certain criteria or to order the results by specific columns.
The used query is rendered as well as the result set found by the executed query.
As you can see, the result set contains the fields as defined in “Select fields” previously. It’s also possible to edit such a record and to display information about that record.
If that result fits your needs, the backend user might save that query for later use by entering a proper name at the “Load/Save Query” section.
