Report.rst

Report

QFQ Report Keywords

See :ref:`qfq_keywords`.

QFQ content element

The QFQ extension is activated through tt-content records. One or more tt-content records per page are necessary to render forms and reports. Specify column and language per content record as wished.

The title of the QFQ content element will not be rendered. It's only visible in the backend for orientation of the webmaster.

To display a report on any given TYPO3 page, create a content element of type 'QFQ Element' (plugin) on that page.

A simple example

Assume that the database has a table person with columns firstName and lastName. To create a simple list of all persons, we can do the following:

10.sql = SELECT firstName, lastName FROM Person

The '10' indicates a root level of the report (see section :ref:`Structure<Structure>`). The expression '10.sql' defines an SQL query for the specific level. When the query is executed, it will return a result having one single column name containing first and last name separated by a space character.

The HTML output, displayed on the page, resulting from only this definition, could look as follows:

JohnDoeJaneMillerFrankStar

I.e., QFQ will simply output the content of the SQL result row after row for each single level.

Format output: mix style and content

Variant 1: pure SQL

To format the upper example, just create additional columns:

10.sql = SELECT firstName, ", ", lastName, "<br>" FROM Person

HTML output:

Doe, John<br>
Miller, Jane<br>
Star, Frank<br>

Variant 2: SQL plus QFQ helper

QFQ provides several helper functions to wrap rows and columns, or to separate them. E.g. fsep stands for field separate and rend for row end:

10.sql = SELECT firstName, lastName FROM Person
10.fsep = ', '
10.rend = <br>

HTML output:

Doe, John<br>
Miller, Jane<br>
Star, Frank<br>

Check out all QFQ helpers under :ref:`qfq_keywords`.

Due to mixing style and content, this becomes harder to maintain with more complex layout.

Format output: separate style and content

The result of the query can be passed to the Twig template engine in order to fill a template with the data.:

10.sql = SELECT firstName, lastName FROM Person
10.twig = {% for row in result %}
    {{ row.lastName }}, {{ row.firstName }<br />
{% endfor %}

HTML output:

Doe, John<br>
Miller, Jane<br>
Star, Frank<br>

Check out :ref:`using-twig`.

Syntax of report

All root level queries will be fired in the order specified by 'level' (Integer value).

For each row of a query (this means all queries), all subqueries will be fired once.

• E.g. if the outer query selects 5 rows, and a nested query select 3 rows, than the total number of rows are 5 x 3 = 15 rows.

There is a set of variables that will get replaced before ('count' also after) the SQL-Query gets executed:

{{<name>[:<store/s>[:...]]}}

Variables from specific stores.

{{<name>:R}} - use case of the above generic definition. See also :ref:`access-column-values`.

{{<level>.<columnName>}}

Similar to {{<name>:R}} but more specific. There is no sanitize class, escape mode or default value.

See :ref:`qfq_keywords` for a full list.

See :ref:`variables` for a full list of all available variables.

Different types of SQL queries are possible: SELECT, INSERT, UPDATE, DELETE, SHOW, REPLACE

Only SELECT and SHOW queries will fire subqueries.

Processing of the resulting rows and columns:

• In general, all columns of all rows will be printed out sequentially.

• On a per column base, printing of columns can be suppressed by starting the column name with an underscore '_'. E.g. SELECT id AS _id.

  This might be useful to store values, which will be used later on in another query via the {{id:R}} or {{<level>.columnName}} variable. To suppress printing of a column, use a underscore as column name prefix. E.g. SELECT id AS _id

Reserved column names have a special meaning and will be processed in a special way. See :ref:`Processing of columns in the SQL result<Processing of columns in the SQL result>` for details.

There are extensive ways to wrap columns and rows. See :ref:`wrapping-rows-and-columns`

Using Twig

How to write Twig templates is documented by the Twig Project.