Tutorial

Note

The tutorial is intended for new employees in the QFQ team.

Tutorial DB

Open phpMyAdmin and execute Tutorial.example.sql in your personal database. This will create the tables and data required for the upcoming tasks.

With MariaDB (a fork of MySQL) you can work directly via the command line, or use one of the many MySQL database frontends. In the QFQ team, phpMyAdmin is primarily used (link to the current installation see above).

For the introduction, we recommend using a frontend, not the command line.

SQL - Basic

_images/tutorial-erd-simple.png

Source DrawIO: tutorial-erd.drawio

Solutions to the tasks

Task 1 - SELECT

  1. Output the names of all books.

  2. Output the names of all books, except the book with the name “The Hobbit”.

Task 2 - JOIN, NOW, CONCAT

a) Output the names of all books in the first column and the last names of the authors in the second column.

b) Output a list of all current, non-overdue rentals with the name of the book, the name of the person currently borrowing it, and the due date (“end”). For a current (non-overdue) rental, the current time must be between “start” and “end” and the book must not yet have been returned (returned = 0).

c) Output a list containing in a single column all books like this: “The book ‘(Bookname)’ was written by ‘(Author Firstname)’ ‘(Author Lastname)’”

Task 3 - ORDER BY, LIMIT

  1. Output the names of all books in reverse alphabetical order.

b) Sort the result of task 2b by due date (ascending). Show only the first three results.

Task 4 - COUNT, SUM

  1. Output the number of books by the author “Dan Brown”.

  2. Output the total number of pages of all books.

Task 5 - GROUP BY, LEFT JOIN, GROUP_CONCAT, NULL

a) In the first column, output the names of all books, and in the second column all rental dates of the book (“start”) separated by commas.

b) In the first column, output the names of all authors (persons who have written at least one book), and in the second column all works of the respective author (comma separated).

Task 6 - IF, DATE_FORMAT

a) In the first column, output the names of all persons. In the second column, output “yes” if the person has already borrowed a book. Otherwise, output “no”.

b) In the first column, output the names of all books. In the second column, output “available now” if the book is currently not borrowed. If the book is currently borrowed, then output “available from (dd.mm.yyyy)”.

Task 7 - INSERT

a) Insert a new book into the database. The name should be “New Book”, it has 100 pages and was written by the author with id 1. Write down or remember the id of the new book. Use the SQL INSERT statement. Do not use the phpMyAdmin Insert function.

Task 8 - UPDATE

a) Update the new book using the id remembered earlier. Set the name to “Revised New Book” and the number of pages to 101.

  1. Change the author of all books to “J. R. R. Tolkien” (do not update via id).

Task 9 - DELETE

  1. Delete everything from the Rental table.

  2. Delete the book with the id from task 7.

Task 10 - Grp, Glue

Grp and Glue are not SQL keywords. They are a best practice we often use, see Multi purpose tables & column keys.

a) Look at the following diagram and think about what Grp and Glue could stand for. Replace Grp and Glue with other table names that would make sense in this context. (For now ignore that Grp has a recursive relation with itself.)

_images/tutorial-erd.png

Source DrawIO: tutorial-erd.drawio

  1. Why is the table called ‘Grp’ and not simply ‘Group’?

  2. What do we generally achieve by using Grp and Glue?

  3. Why does Grp have a ‘grId’ that points to another Grp?

  4. Think of a scenario in which Grp is connected via Glue to the table ‘Person’.

Example of Grp and Glue interaction with tables Person and Book

_images/grp_glue.png

  • Glue as a Bridge:

    • The ‘Glue’ table serves as a connection between the ‘Grp’ table and the ‘Person’ and ‘Book’ tables.

  • Understanding Glue Columns:

    • The ‘Glue.xId’ represents the id from either the ‘Person’ or ‘Book’ table, depending on which entity it is connecting.

    • The ‘Glue.grId’ corresponds to the ‘Grp.id’, creating the link between an entity (Person or Book) and a group.

  • Practical Example:

    • For instance, ‘Book_1’ (with ‘id:1’) is connected to ‘grId:2’, which in turn links to ‘Grp.id:2’, identifying it as part of the “Fantasy” genre.

  • Grp Hierarchy:

    • The ‘Grp’ table has a hierarchical structure where rows with ‘grId:0’ act as parents. The child records have their grId matching the id of the parent, establishing the parent-child relationship within the groups.

SQL - Advanced

Tip

GROUP BY Always specify, if aggregate functions are used - if not, MySQL will do it, which can be different from what is expected.

Performance

  • GROUP BY

    • If possible, use AND to minimize resultset.

    • Do not use GROUP BY if you are not understanding why it is necessary.

  • SUBSELECT()

    • Remember: for each selected row of the outer SELECT, the subselect is fired.

    • Example: Outer SELECT has 100 rows, than 101 queries are fired.

Warning

SELECT p.id FROM Person AS p WHERE p.id in (SELECT adr.pId FROM Address AS adr WHERE adr.pId=p.id) This is a huge waste of database performance!

  • Use the same CHARACTER SET and COLLATION on all of your tables. If they are mixed, the indexes can still be used, but MariaDB have to convert it/reread - which practicaly means a deep impact.

Note

ALTER TABLE <table> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Prerequisites Web Development

CMS / Typo3

You will receive an introduction to Typo3; specifically, the following points should be conveyed to you:

  • What is a CMS?

  • Pages (Title, Page Slug, Hide in Menu), PageContent

  • FeUser, BeUser

  • Permissions

HTML Basics

It is assumed that you are already familiar with the basics of HTML. This HTML section is intended to refresh your knowledge.

Task 1 - HTML Table

Create a new Typo3 page and add a Page Content section of type HTML. In it, create the following table using pure HTML (no CSS styling), to refresh your knowledge.

_images/ex1_table.png

Task 2 - Self-study

This is a list of HTML topics and skills. Work through the list. What you already know well, you can skip. If you are not familiar with something, look it up on the Internet (e.g. https://www.w3schools.com/html/ ) and try it directly in your own HTML Page Content!

  • HTML Editors

  • HTML Basic

  • HTML Elements

  • HTML Attributes

  • HTML Headings

  • HTML Paragraphs

  • HTML Styles

  • HTML Formatting

  • HTML Quotations

  • HTML Comments

  • HTML CSS

  • HTML Links

  • HTML Images

  • HTML Tables

  • HTML Lists

  • HTML Blocks

  • HTML Classes

  • HTML JavaScript

  • HTML File Paths

  • HTML Head

  • HTML Layout

  • HTML Responsive

  • HTML Computercode

  • HTML Entities

  • HTML Symbols

  • HTML URL Encode

  • HTML Forms

  • HTML FormElements

  • HTML Input Types

  • HTML Input Attributes

  • HTML Examples

  • HTML Quiz

  • HTML Exercises

  • HTML Certificate

  • HTML Summary

Skills

  • Text formatting (Heading, Bold, Italic)

Links:

  • Fully qualified

  • Relative

  • Parameters

  • Anchor

CSS / Bootstrap

It is assumed that you already have basic knowledge of CSS. If not, you can work through it in self-study. For example, there is a CSS tutorial at https://www.w3schools.com/css/

Instead of writing your own CSS classes or even individual styles per element, we use Bootstrap wherever possible, or the CSS classes delivered with QFQ (QFQ CSS Classes).

Bootstrap is a CSS/JavaScript library that provides a set of unified styles for various elements (tables, buttons, etc.). Bootstrap also includes complete components, such as a modal pop-up dialog or progress bars.

You do not need to worry about including Bootstrap – within your Typo3 instance, Bootstrap is already available. Pay attention to the currently integrated BS version and the corresponding documentation.

Task 1 - Bootstrap Grid

Another important aspect of Bootstrap is that it makes it easy for us to build our page responsive. A responsive page automatically adapts to the screen of the end device on which it is viewed.

Bootstrap solves this mainly with its own grid system, which allows you to build a multi-column layout. The number of columns displayed side by side depends on the screen size. If the screen is too narrow and the grid is set up correctly, then a column automatically moves below another instead of being displayed next to it.

Create a Typo3 page (HTML Page Content) and add a Bootstrap grid with three columns containing any content. The columns should behave as follows:

  • 3 columns should be displayed side by side on large screens (“lg”)

  • 2 columns should be displayed side by side on medium screens (“md”)

  • 1 column should take up the full width on small screens (“sm”)

Use the documentation for grids: https://getbootstrap.com/docs/3.4/css/#grid

Task 2 - Bootstrap Table and Buttons

The previously created Page Content (the table from the HTML Task 1) should now be improved with Bootstrap. Add a “Bootstrap style” to both the table and the buttons that you like. Again, use the documentation: https://getbootstrap.com/docs/3.4/css/#tables and https://getbootstrap.com/docs/3.4/css/#buttons

Task 3 - Bootstrap Form

Create a form with Bootstrap styling that allows the creation of a new book. A book should have the same attributes as seen in HTML Task 1:

  • A book has a title (text field)

  • A book has an author – this should be selectable from a list of authors (dropdown)

  • A book belongs to one or more genres (checkboxes)

  • A book is either available for rental or already rented (radio buttons)

The Bootstrap documentation will be helpful: https://getbootstrap.com/docs/3.4/css/#forms

Note: The form does not need to do anything – when clicking submit, nothing has to happen.

JavaScript / jQuery

You will receive a short introduction to JavaScript and jQuery from your supervisor.

Task 1 - Show / Hide an Element with jQuery

Use JavaScript and (optionally) jQuery to extend the form from the previous task as follows:

  • The radio button “Available” should be set to “yes” by default.

  • When switched to “no”, three new input fields should appear below:

    • A dropdown with a person selection “Currently Rented by”.

    • A date field “Rental Start Date”.

    • A date field “Rental Due Date”.

  • When the radio button is switched back to “yes”, these three elements should be hidden again.

Note: For a date field, there are many possible solutions – native Bootstrap does not include one directly. Search the Internet for a datepicker solution that can be integrated! (e.g. “jQuery Datepicker”)

Basic Knowledge

  • Here only a small collection of important topices are explained.

  • See also Native Class: Extra. Read through it once and come back here.

General Tips

  • The Dev Panel will show latest STORE_RECORD values and others.

  • Debug a value: show value in the FormElement under Layout → Note field.

  • Debug a query: Execute directly on the database. Tools like

    https://www.phpmyadmin.net/ or mysql are useful.

  • Always have a sharp look at the Error / Exception - it helps a lot.

  • In FormEditor, you’ll find the table definition of the primary always at the

    last pill.

  • Define a strategy how to name forms:

    • E.g. person, person-admin, person… (all person forms listed together).

    • For subrecords, some might find it useful to prefix with sub.

    • Per sub-tool / sub-functionality, define prefixes and name the forms accordingly.

  • Add the columns modified and created to every table (Table structure). This helps debugging. Especially in the Glue table, this is useful to directly see whether a (new) entry was created, modified or untouched.

  • Pre-Render: In Report, links/buttons can be created at an early position, save the button in a variable via AS and hide it (AS ‘_link|newPersonBtn|_hide’). Afterwards, simply call the button at the place where necessary:

    <th>{{&newPersonBtn:RE::-}}</th>.

  • In Report work with code blocks {}. Name them by simply writing a label before the opening bracket.

  • Documentation: Sections Store, Form and Report are most important.

  • Under Report → QFQ CSS Classes, Bootstrap and Tablesorter find styling classes for tables in the report.

  • Under Form → Class: Native find explanation of the different FormElement types.

FormEditor: Form

Form Settings

Pill: Basic

Field: Name

The name under which the form is saved.

Field: Title

The headline of the form, visible to the user. It can be dynamically adjusted by logic – depending on the usage of the form.

{{SELECT IF( {{id:R0}} = 0, "Add a Genre", "Edit a Genre") }}

If a new form with ID 0 is created, Add a Genre will be displayed, otherwise Edit a Genre. The R stands for the Record Store. How it works and what it contains at what time can be found in the Store chapter.

Field: Note

Note for the developer in case they want to describe the form in more detail.

Field: Table

The table the form points to. The records can directly access the columns of this table without having to be selected first.

Pill: Form Element

This is where the elements that make up the form are created.

Pill: Layout

Field: Show Button

This sets which buttons in the top right of the form can be used by the user. The New button is important here, because otherwise the user could create new entries unintentionally.

Field: Label Align

Choose to which side the content should align within its field. This field is defined by the columns.

Pill: Table Definition

This displays all columns from the table that was selected under Basic → Table.

FormEditor: FormElement

Pill: Basic

Field: Name

  • The name points directly to the table columns selected in the form.

  • Under Table Definition you can see the table with the column name/type/size/default.

  • Non-primary column names:

    • If a column from another table should be displayed, the name can be freely chosen as long as it does not match a column from the primary table (some more action is necessary to use read/save values).

    • Best practice is to start non-primary columns with my. Choose a self-explanatory name to make it’s use obvious.

Field: Label

This is the name you give the FormElement to distinguish it from others – especially if there are two or more pointing to the same column.

Field: Mode

The mode determines the behaviour. The names are self-explanatory.

Field: Mode SQL

Here the mode can be dynamically adjusted to the FormElement based on logic.

{{SELECT IF( '{{roleUser:UE}}' = 'course_role_admin', 'required', 'readonly' ) }}

In this example, it checks which role the user has and decides based on this role whether they may edit the content or not. The role comes here from the User Store.

Field: Class

Categorize FormElements:

  • Native: fields the user interacts directly with.

  • Actions: consist of logic executed in the background, e.g. validation so the same value cannot be selected twice. Be aware of FormElement.parameter.sql…. in native FormElements - they behave very similar to FE.class=action.

  • Container: are different sections in which you can group FormElements. They are explained in more detail in the tutorial.

Link to documentation Class: Container

Field: Type

Link to documentation Class: Native

Field: Value

The Value field is meant for outputting individual values – whether selected or hardcoded. This value can also be used in sql1, if it is e.g. an ID.

SELECT id AS _id, p.firstName FROM Person

The _id then takes the value that is in the Value field.

Field: sql1

This field is used for SQL queries and is mainly intended for when more than one value is returned. That is also why the ! is in front of the SELECT – it means the values will be returned as an array and not as a string.

Field: Parameter

In this field, properties are defined that every FormElement can have. For this you can look in the documentation of the individual Native classes. There the available parameters are always described.

Concept: Store

Link to documentation Store

STORE_RECORD

The Record Store is filled automically during form load with the given record. However, it needs an ID to know which person it should load. That’s why it is filled when editing a form, because the edit button passes the ID of e.g. a person. When leaving the page or reloading, the Record Store is overwritten with new data.

STORE_FORM

This store is filled with the inputs a user makes in a form in the moment when the user presses ‘save’. It is often used together with the Record Store. The Form Store is overwritten with latest user data on each ‘save’.

STORE_USER

This store is persistant during the user (=browser) session (limit: timeout or browser restart). Data remain in the store as long as the user is logged in and are overwritten when the user logs out.

Miscellaneous

Subrecord

If a FormElement Subrecord is created in a form, it establishes the connection between e.g. a specific person and the roles. In the Parameter field, the detail-form (=sub form) is referenced. E.g. all roles assigned to a person. In the detail form you then create a FormElement Select, where all roles, that a person can be assigned to, are offered to choose. This ensures that in the first step a specific person can be chosen and in the second step all roles are displayed.

Since we usually look at a linking table (Glue) in the subrecord, but must come from the Person table, we must specify this connection in the Parameter field.

Parameter
form = subSettingRole
detail = id:xId

subSettingRole is my second form with the Select. The ID comes from the Person table and the xId belongs to the Glue table. If I want to assign a role to a person, this happens by creating a Glue record. That’s why the first form selects Glue.id AS _id and the role Grp.name AS Role. Important: The first form only selects – it does not write.

The second form points to the Glue table, because this form writes an entry into the Glue table. The FormElement points to Glue.grId. Thus, a new Glue entry can be created:

  • ID auto generated,

  • xId from the parameter,

  • and the grId is now selected.

So in the sql1 field the gr.id (linked to the grId) and the role gr.name are selected.

Action

Link to documentation Class: Action

beforeSave Validator

In the FormElement under Basic, action is selected and the type beforeSave. Our code goes into the Parameter field and starts with sqlValidate = …. Then follows a normal SQL query where we fetch one value from the DB and one from the Form & Record Store.

It is also important here to specify the correct sanitize class (e.g. alnumx, digit, etc.). The alert is a pop-up with selectable content.

sqlValidate = {{!SELECT p.account
              FROM Person p
              WHERE p.account = '{{account:FRE:alnumx}}'
                }}

expectRecords = 0
alert = Account name already exist

Further information: Class: Action.

Action in Typo3 Report

In my case, I created a button that opens a pop-up with an “Execute Yes/No” question. If you click Yes, the action is executed.

The button looked like this:

CONCAT('p:{{pageSlug:T}}?action=dropEnroll&enrollId=', e.id ,'|b|s|D|q:Do you realy want to drop the course?') AS '_link|studentCancelEnroll'

Action now contains dropEnroll and enrollId contains the e.id from the respective table row. Since the button stores these in the SIP Store, we first pass them into the Record Store.

  {
    sql = SELECT '{{enrollId:SE}}' AS _enrollId
                 ,'{{action:SE::w}}' AS _action
}

Through AS, both are loaded from the SIP into the Record Store. Additionally, we wipe the SIP Store with the small w to avoid errors by using browser back/forth. Afterwards we can make changes with normal SQL statements that the button should perform.

In my example, that would be a status change.

parent {
  sql = SELECT '' FROM DUAL WHERE '{{action:RE}}' = 'dropEnroll'

  child {
    sql =  UPDATE Enrollment SET statusId = (SELECT grs.id
                                                    FROM (Grp grs)
                                                    WHERE grs.reference = 'course_status_dropped'
                                            )
              WHERE '{{enrollId:R0}}' = id
  }
}

Since I query the action variable in the Dual table, I can execute multiple child blocks if desired. It can also be done – as here – in a single SQL statement:

{
    sql = UPDATE Enrollment SET statusId = (SELECT gr.id
                                                FROM Grp gr
                                                WHERE gr.reference = 'course_status_dropped')
            WHERE '{{enrollId:R0}}' = id
            AND '{{action:RE}}' = 'dropEnroll'
  }

Here the whole thing was solved via a subselect.

Native Class: Extra

I used this class to set IDs in a table. In a registration table, the user had filled out very little, the rest were all IDs from other tables.

Here I used the native class extra to connect these IDs via SELECT. It is a class that runs in the background together with the form.

Since I set an ID, I used the Value field to select it. Once I also just used an ID from a store.

QFQ

In this tutorial you will learn what QFQ is and how to use its many features. For each section there is an exercise to practice.

What is QFQ?

QFQ (Quick Form Query) is an extension for the Typo3 CMS. Its backend is written in PHP and its frontend in Javascript. First and foremost it allows the developer to connect their website to a custom database and display dynamic content. But displaying data is not everything QFQ is good at. QFQ stands for ‘Quick Form Query’ and at its core is an easy to use but powerful Form Editor. The Form Editor allows the developer to build custom forms that allow for user input, which can then easily be stored in the connected database.

So QFQ can display dynamic content and process user inputs. But this is not everything! QFQ comes with many more features, such as:

  • Sending emails (optional with attachment, maybe dynamically generated PDFs).

  • Execute a script (shell script, PHP, …) on the webserver and then process its output.

  • Execute SQL

  • Access to Typo3 Variables such as the currently logged in user, his/her user groups, etc.

  • and much more!

Report

When we talk about a Report in QFQ, what we really mean is a Typo3 page content of the type ‘QFQ Element’. In the most common and simple case, we just use Reports to take data from our database and show it to the user. However, reports are very powerful and can be used for anything you want on your website. The one thing reports are not meant for are forms to handle user input. Technically you could build your own form on a report, but QFQ comes with its own Form Editor feature and we highly recommend to use that instead.

Basic Report Syntax

Let’s start at the beginning. The first thing we want to do is to take data from our database and display it on our website. For this we are going to use - you guessed it - a report! Before we can start, we have to create a Typo3 page and add a page content of type ‘QFQ Element’ to it. When we edit this page content, we are greeted with a large empty box where we are supposed to write our QFQ code. QFQ code works by directly executing SQL queries, and using the result of these queries to render plain HTML. To do this, QFQ code has its own syntax. It’s really easy - start by looking at the following code:

{
  sql = SELECT title FROM Book
}

{
  sql = SELECT lastName FROM Person
}

The first thing to notice is that this code is divided into two blocks. Curly braces are used to define what’s inside these blocks.

You will also see 10 {} in older code, which stands for the order of execution, where 10 {} executes before 20 {} regardless of position. This is deprecated and no longer best practice and has been replaced by top-down code execution.

Inside each block you see an SQL statement. SQL statements are the core of each block, and since the whole report is made out of blocks, the SQL statements are the most important part of a QFQ report. The SQL is preceded by sql =, which is necessary because a block can consist of more than just an sql statement. But more on that later.

What’s the result of this simple report? It is something like: The HobbitDonQuixoteTheDavinciCodeTolkiende CervantesBrown. First we selected the titles of all books - and they are automatically rendered as the output of the QFQ code. The same goes for the lastnames of all People.

The information in this form is of little use to a visitor of our website. What we need is formatting! So let’s add something to our first block:

{
  sql = SELECT title FROM Book
  head = <ul>
  tail = </ul>
  rbeg = <li>
  rend = </li>
}

The SQL statement is still the same - but we have introduced 4 new keywords (you’ll find all keywords under QFQ Keywords (Bodytext)):

  • The head will be rendered before any of the sql. However, if the SQL returns nothing (0 rows), then no head will be rendered. If you want to render the head even if the SQL returns nothing, use shead instead (‘static head’).

  • The tail will be rendered after all of the sql. However, if the SQL returns nothing (0 rows), then no tail will be rendered. If you want to render the tail even if the SQL returns nothing, use stail instead (‘static tail’).

  • rbeg stands for ‘row begin’. rbeg will be rendered before every row of the sql result.

  • rend stands for ‘row end’. rend will be rendered after every row of the sql result.

And just like that, the output of our first block is now formatted HTML:

<ul>
  <li>The Hobbit</li>
  <li>Don Quixote</li>
  <li>The Da Vinci Code</li>
</ul>

Let’s go a bit further for with the second block. We’ll change it to:

{
  sql = SELECT firstName, lastName FROM Person
  head = <table class="table">
           <thead>
             <tr>
               <th>Firstname</th>
               <th>Lastname</th>
             </tr>
           </thead>
           <tbody>

  tail =   </tbody>
         </table>

  rbeg = <tr>
  rend = </tr>
  fbeg = <td>
  fend = </td>
}

First of all, we are no longer using an unordered list to display our data here. We are now rendering an HTML table. We can use the head to create column headings too. Furthermore, in our SQL query we now select firstname and lastname. The result of this SQL query is a table with two columns. This means there are two fields per row now - a chance to build in some additional formatting:

  • fbeg can be used to render something before each field (but after rbeg).

  • fend can be used to render something after each field (but before rend).

And just like that we have changed the output of the second block to:

<table class="table">
  <thead>
    <tr>
      <th>Firstname</th>
      <th>Lastname</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>J. R. R.</td>
      <td>Tolkien</td>
    </tr>
    <tr>
      <td>Miguel</td>
      <td>de Cervantes<td>
    </tr>
    <tr>
      <td>Dan</td>
      <td>Brown</td>
    </tr>
  </tbody>
</table>

Note: There are even more QFQ body keywords such as rsep and renr! Please check QFQ Keywords (Bodytext).

Exercise 1

  • Create a new page ‘Person’ and add a QFQ content Element with the above QFQ code to it. (The code to create a table that contains all people)

Exercise 2

  • Add another column to that table which contains the email-address of the person

Exercise 3

  • Create another page, call it ‘Books’ and add a QFQ Element to it.

  • Display a table with all books on this page.

  • Add at least two columns - one for the title and one for the name of the author.

Nested Blocks and the Record Store

There are a few things going on in the following example:

parent {
  sql = SELECT '<h1>', firstName, lastName, '</h1>', id AS _pId
          FROM Person

  child {
    sql = SELECT title
            FROM Book
            WHERE authorId = {{pId:R}}

    head = <ul>
    tail = </ul>
    rbeg = <li>
    rend = </li>
  }
}

We can see a parent block - parent { } - and a child block - child { }. The child block will be executed for each row that is returned by the parent block’s SQL statement. If we compare this with conventional procedural programming, we could say that the nested blocks create a for each loop.

But there is more going on in the example:

  • You might have noticed that the ‘h1’ Element is directly part of the SQL

    SELECT statement. The same could have been achieved by using the rbeg and rend attributes of the block. In fact, we advocate for the use of these attributes. Nevertheless, the possibility of including HTML directly in the SQL statements can be really useful sometimes!

  • In the parent block, we selected id AS _pId. In plain old SQL, writing id AS pId (underscore is missing) would be used to set the column heading to ‘pId’. However, QFQ is doing more. By giving the id-field an alias (pId), QFQ automatically creates a variable named pId.

    • This variable is put into the Record Store. There are several places where QFQ can store variables and the record store is arguably the most used. Other ‘Stores’ - places to store variables - will be discussed in later chapters.

    • By putting the underscore (AS _pId) in front of the alias, we can prevent QFQ from rendering this specific field. We don’t want random id-numbers appearing on our page, we just want to remember the id because we need it in the nested block.

    • In the nested block’s SQL statement we access the id we have stored before. We do this by typing {{pId:R0}}. The double curly brackets are just the way to tell QFQ that we would like to access a variable. ‘pId’ is obviously the name of the variable we want. This is followed by ‘:R’, meaning that we fetch it from the Record Store. The zero at the end means that we replace this term with 0 if there is no variable ‘pId’.

The result of our QFQ code might look like this:

<h1>J. R. R. Tolkien</h1>
<ul>
  <li>The Hobbit</li>
  <li>The Lord of the Rings</li>{id:
</ul>
<h1>Miguel de Cervantes</h1>
<ul>
  <li>Don Quixote</li>
</ul>
<h1>Dan Brown</h1>
<ul>
  <li>The Da Vinci Code</li>
</ul>

Exercises

  1. Create a new page and call it ‘Genres’. Add a QFQ Element to it. Write QFQ code that generates a heading (@<h2>@) for each genre. Hint: We are storing genres in the table Grp, but not all records in this table represent a genre. Only the records with @grId = [id of the Grp record with reference = 'library_genre']@ are genres.

    Below each genre-heading, display a list (@<ul>@) of all Books in that genre. Use a nested QFQ block and make use of the record store!

Client-Store, Zero and Empty

In the previous chapter it was briefly mentioned that the Record Store is not the only place QFQ can get data from. Introducing: The Client-Store! First we should explain how data is put into the client store. This is simple:

  • Existing HTTP POST variables are automatically put into the Client-Store.

    • This means that user data, which is submitted to the page via form, is in the Client-Store.

  • All parameters of the user’s requested URL are in the Client-Store.

To access values from the Client-Store in a QFQ report, we can use:

{{variableName:C}}

However, you are more likely to be using it in one of these two ways:

{{integerName:C0}}
{{stringName:CE:alnumx}}

The first example is very similar to the one shown at the beginning. The difference is that we write:

{{integerName:C0}}

instead of {{integerName:C}} after the variable name. This should be used if you are sure that the variable contains an integer. The 0 at the end simply means that the whole expression will be replaced by 0 instead of the variable’s value, if the variable does not exist.

The second example,

{{stringName:CE:alnumx}}

should be used if the variable contains a string. Here’s what’s important:

  • Instead of 0, we append E after the C that stands for “Client-Store”. E means “Empty” – the whole expression is omitted if the variable does not exist.

  • There is another colon after CE, followed by alnumx. This is the sanitize class. With alnumx QFQ outputs only numbers, Latin letters and a few special characters. If the value does not match, an error occurs. See the official documentation.

  • Any variable retrieved from the client store uses digit as the default sanitize class. Therefore we must set a class explicitly for strings, but not for integers.

  • If the variable is a string and you use it inside an SQL statement, add quotes around it (e.g. '{{stringVariable:CE:alnumx}}'). Otherwise the resulting SQL is invalid.

Exercise 1

  1. Create a page and call it Book detail (add a QFQ content element).

  2. If the URL contains a parameter bookId, then title, author and number of pages for the book with this id should be displayed.

  3. If there is no parameter bookId in the URL, then display the text No book selected. Example URL: http://webwork20:<port>/home/bookdetail

  4. If a bookId was stated in the URL, but there is no book with this id in the DB, display Book not found. Example URL: http://webwork20:<port>/home/bookdetail?bookId=111

Tips

The value (most likely HTML code) that is assigned to althead is displayed when the SQL in the same block returns nothing:

{
  sql = ...
  althead = <p>Alt Element</p>
}

You can use the DUAL table and '' to use WHERE in SQL. In SQL, DUAL is a special dummy table used to select a value or perform a function when no actual table data is needed:

{
  sql = SELECT ''
          FROM DUAL
         WHERE <Condition>
  althead = <p>Alt Element</p>
}

In this example we now SELECT something if the condition is TRUE and if FALSE we return the <p>Alt Element</p>.

  1. On the page Book, add a link to the Book Detail page for each book (add a new column or build a link directly around the book title).

Integrating Forms on our Report

Here it all comes together. Let’s make a page on which we can see a table that lists all books. We also want an Edit button for each book, as well as a New Book button. Our goal is to link these buttons to forms we will create later on!

We are using the following QFQ code. This time the code should explain itself, because we have added comments to it. Comments in the right place can be invaluable. In QFQ, you can start a comment with the # symbol. Inside SQL queries you can also use block comments.

If there is a form in the SIP, it will be displayed!

form = {{form:SE}}

#   ?form=Book   => clicking on the link opens the Book form
#   &r=0         => the Book form is opened with record id = 0.
#                   This means a new book will be created instead of
#                   opening the form with data of an existing book
#   |N           => (NEW) Display a button with a plus-symbol instead of
#                   an unformatted link.
#                   If you want your link formatted as a button, but still
#                   choose your own text, you can use |b|t:Buttontext
#
#   AS '*link|newBookButton|*hide'  => link is a special column name and
#                                      even with the underscore in front
#                                      of it, it will be rendered.
#                                      Unless we add '_hide' (another
#                                      special column name)!
#                                      To have a reference to the created
#                                      link, we give it a custom name
#                                      (newBookButton).
#                                      Because we are using more than one
#                                      name / special column name,
#                                      we have to separate them with pipes
#                                      (|) and put single ticks around the
#                                      whole expression.
{
  sql = SELECT 'p:{{pageSlug:T}}?form=Book&r=0|s|N' AS '*link|newBookButton|*hide'
}

Render table with all books. We insert the ``newBookButton`` in the table header

# &r=...                      => we set the record id to the book's id, so
#                                that our form will load the data of that
#                                specific book when opened
# {{&newBookButton:RE::-}}    => The '&' means, that we want the computed
#                                value of the link, and not the original string!
#                                If we don't use the '&', we'll simply get
#                                the string 'p:/books&form=Book&r=0|s|b|N'
#                             => Notice the RE::-? After the store (RE)
#                                we could specify a 'Sanitize Class', which
#                                we don't need.
#                                This is why there is nothing between the
#                                two colons.
#                                After the next colon we can specify the
#                                escape/action class. The dash (-) means
#                                'no escaping'.
#                                This is needed here, because
#                                '{{&newBookButton:RE}}' contains an
#                                HTML button and some of the characters are
#                                by default escaped by QFQ,
#                                resulting in invalid HTML if we don't
#                                tell QFQ not to escape it.

{
  sql = SELECT CONCAT('p:{{pageSlug:T}}?form=Book&r=', b.id, '|s|b|E') AS _link,
              b.title,
              CONCAT(p.firstName, ' ', p.lastName)
        FROM Book AS b, Person AS p
        WHERE p.id = b.authorId

  shead = <table class="table">
            <thead>
              <tr>
                <th>{{&newBookButton:RE::-}}</th>
                <th>Title</th>
                <th>Author</th>
              </tr>
            </thead>
            <tbody>

  stail =   </tbody>
          </table>

  rbeg = <tr>
  rend = </tr>
  fbeg = <td>
  fend = </td>
}

Finished page Code

form = {{form:SE}}

{
  sql = SELECT 'p:{{pageSlug:T}}?form=Book&r=0|s|N' AS '*link|newBookButton|*hide'
}

{
  sql = SELECT CONCAT('p:{{pageSlug:T}}?form=Book&r=', b.id, '|s|b|E') AS _link,
              b.title,
              CONCAT(p.firstName, ' ', p.lastName)
        FROM Book AS b, Person AS p
        WHERE p.id = b.authorId

  shead = <table class="table">
            <thead>
              <tr>
                <th>{{&newBookButton:RE::-}}</th>
                <th>Title</th>
                <th>Author</th>
              </tr>
            </thead>
            <tbody>

  stail =   </tbody>
          </table>

  rbeg = <tr>
  rend = </tr>
  fbeg = <td>
  fend = </td>
}

Here’s what we see when we open our new book-page!

Book page with empty table

Since there are no books in our database, our table only has one row – the header! By the way, if we hadn’t used shead and stail (and used head / tail instead) we wouldn’t see anything at all. The + button doesn’t do anything yet, because we have not created the Book form. We will do that in the next chapter.

Exercises

  1. Modify your existing Book page so that there is a button to create new books and a button for each book to edit (just like in the example above). You don’t need the Form yet, assume its name is Book.

  2. Do the same for your Person page. Assume the Form name is Person.

Forms

Let’s talk forms. Handling complex user input within a report would be entirely possible, but QFQ has another special solution for that – the Form Editor. The Form Editor is a Form that allows you to create forms – it’s Formception!

Before we can manage our forms, we need to set up the page on which we do that. You can simply create a Typo3 page, call it Forms and add a QFQ Element to it with the content:

file=_formEditor

In this case, we are loading the integrated QFQ code for the form management page from a file, so we don’t have to code a custom solution for that. You should also set the page slug for this Typo3 page to /form. You can do this by editing the URL Segment in the General tab when editing the Typo3 page.

If you open your Forms page on the frontend, it should look like this:

Form management page

For now you can ignore all the Json buttons – we will be working with the normal create/edit feature.

Note

Make sure your average user does not have permission to access the Forms page before your website goes live!

Creating a Form

Note that we are still using our Library-Database. The definitions of tables and columns are important when creating a form, because we will use that form to save data to these tables!

First we want to create a form on which we can edit Books for our library! We create a new form by clicking the + button on the top left side of our Forms page. We are now in the Form Editor! There are several tabs (we also call them pills) that will appear after we save, but for now we just fill out the Basic tab and then hit the save button in the top right:

Form Editor basic tab

Form fields to fill in:

  1. Name – Choose a name for your form, you will need it later to refer to the form. Since we will use the form to create books we will simply call it Book.

  2. Title – The title is displayed at the top of the form (where the arrow is pointing in the screenshot above).

  3. Note – You can take notes here – they will not be displayed to the user.

  4. Table – Choose the table from your database for which you want to create/edit records. We choose the table Book, because we want to create and edit books with our form! Think of this as the primary table of the form. It is still possible to show data from other tables, but anything from the chosen table will be much easier.

Exercise 1

  1. Set up the Forms page if you haven’t already.

  2. Create two forms: one called Person (table: Person) and one called Book (table: Book).

FormElements

Now that our form is created we can start adding FormElements to it. FormElements fall in one of three classes:

  1. Native FormElements – the basic building blocks of your forms. They are either input elements such as textfields, checkboxes and dropdowns or elements to display data, like the note element.

  2. Container FormElements – elements used to group together other form elements. You have already seen the tabs/pills in the Form Editor itself – these tabs are container FormElements.

  3. Action FormElements – elements which are not displayed on the form, but they contain a trigger event and an action that is executed when triggered.

For our Book form we will stick to Native FormElements. To add a FormElement, we switch to the Formelements tab and click the + button. Another form opens, which allows us to define our first FormElement:

Add FormElement

FormElement fields to fill in:

  1. Name – In many cases, the name of your FormElement has to be exactly the name of a column on the primary table you chose for your form. In this example we are creating a textbox for book titles. Since we chose Book as our primary table and title is the column on that table, we simply choose title.

    If your FormElement does not represent a specific column on the chosen primary table, we suggest following a naming convention. We usually start these names with my – e.g. myTitle.

  2. Label – The label that will be displayed next to our textbox. Title makes total sense here, because the user should type a title.

  3. Class and Type – You can see the aforementioned FormElement class native here. Because we want a simple textbox, we can leave both class and type at their default values.

Now we click the save icon on the top right side, and then the close icon right next to it. We are now back in our other form and we can see that our Form Element for the title was added to the list!

Added FormElement

This form is still very basic, but let’s see if it works! To do that we can open the Books page we have created in the previous chapter and click the + button in the top left. Our little form opens up! It looks like this:

Basic book form

Exercise 1

  1. Recreate the Book Form with the title FormElement.

  2. Create a new form: * Name the form Person and set its table to Person. * Add two text inputs, one for firstName and one for lastName.

SELECT Statements and Variables inside Forms

Our form does not exactly behave the way we want: When we enter a title for a book and hit save, then we get a message that our book was saved. The new book also appears in our database, meaning it was really saved. But the form’s title still says Create New Book, even though the form contains

Exercise 1

  1. Modify the title of your Book form so that it displays either Create or Edit (as seen in this chapter).

  2. Do the same for the Person form.

Check & Order

Now we have two FormElements. They are displayed in the same order as we have added them. But what if we want to display the author dropdown first and the title textfield second? In the list of FormElements you can simply drag and drop FormElements to change their order.

This automatically adjusts the Order field of the FormElements. We can also set a value for Order manually in the Check & Order tab of the FormElement.

Check & Order tab

Apart from ordering, this tab is all about data validation. With the field Check Type we can define what kind of input is allowed for the FormElement and what’s not. For example you might have an input text field for an email. Simply select the Check Type email and you are done! QFQ will stop the user from submitting the form, if the user hasn’t entered a valid email.

For our Book form, we are adding a FormElement of type text where the user can enter the number of pages the book has. What we need here is Check Type digit to allow integers only! But actually we don’t have to bother, because the Check Type is on auto by default and QFQ noticed that our nrOfPages are stored as an integer in the database. That’s why auto mode chooses digit for us and QFQ even adjusts the text input element to optimize it for numbers:

Digit check type example

So you probably won’t have to come to the Check & Order tab very often, because QFQ is auto-checking and the ordering is easier to do by dragging and dropping. But in cases like email it is very useful. In a more complex scenario you can choose Check Type pattern and write your own regular expression.

Exercise 1

  1. Add the Number of Pages FormElement you have seen in this chapter to your Book form.

  2. Add a FormElement to the Person form where the person’s email address can be entered. The field should only accept valid email addresses.

Layout

QFQ is delivered together with Bootstrap! That’s why we can use Bootstrap classes anywhere on our website and also why QFQ forms already have that Bootstrap look.

By default, our FormElements are placed inside a row which takes up the full width of the form. Furthermore, each FormElement is divided into three areas:

  • Label – Displays the text or HTML from the Label field in the Basic tab of the Form Editor.

  • Input – Displays the chosen input element from the Type field in the Basic tab of the Form Editor (e.g. a textbox, dropdown, etc.).

  • Note – Displays the text or HTML from the Note field in the Layout tab of the Form Editor. (You can see this field on the next screenshot)

Each of these areas has a width assigned to them. The default is 3 units for the label, 6 for the input and 3 for the note – making a total of 12! As you might know, a Bootstrap (BS) row is 12 units wide. You can change the individual widths of the 3 areas in the Layout tab of the Form Editor.

Form layout tab

Suppose you wanted to display the label above the input. To achieve this you can simply give the label a width of 12 units – making a full row and forcing the following elements below the label. Specifically, you would type 12 into the field BS Label Columns. You can also use the full Bootstrap classes with screen size conditions like col-lg-6 col-md-12.

In the screenshot above you see the whole Layout tab, so let’s go through some simple but useful options we have there:

  • Label / Input / Note – with these checkboxes you have full control over the HTML tags that will be rendered for the three areas. You could even uncheck /row for a FormElement and uncheck row for the following FormElement. In the resulting HTML both FormElements will share the same row. This then allows you to place them next to each other!

  • Maxlength – The maximum length of characters which the user may enter. This is only relevant for text inputs.

  • Note – Text or HTML to display in the Note area, which is to the right of the input by default.

  • Tooltip – A text to be displayed when the user hovers over the Form Element.

  • Placeholder – The placeholder text will be displayed if the user has not typed anything yet. This is only relevant for text inputs.

Keep in mind that you can use {{qfqVariables:RE}} and {{SELECT 'Statements'}} in these fields within the Form Editor. Especially in the Note field this can be very useful.

Exercise 1

  1. The Number of Pages input field looks too wide. Make it smaller!

  2. Modify the firstname and lastname FormElements on the Person form so that they only take one line and have placeholders. It should look like this:

    Person form layout with placeholders

  3. Also acceptable:

    Alternative layout variant

Value

Let’s talk about the Value tab in the FormElement Editor. In total there are three fields in the Value tab:

  1. value This field can be used to specify the default value of the FormElement when the form is opened. If a form is opened with an existing record (r > 0) then the value of the FormElements is loaded from the corresponding column (as configured in the FormElement’s name) in the database. This means that if we want a default value, we will often have to write a statement like this:

    {{SELECT IF( {{id:R0}} = 0, 'New Book', '{{title:RE}}' )}}

    This expression will fill a text field with New Book if a new book is being created, but keep the existing book’s title if the form is opened to edit an existing record.

  2. sql1 If a FormElement requires more data, this field can often be used to define that data in the form of an SQL SELECT statement. We have already used this to generate all the options when we were creating a dropdown.

  3. parameter FormElements can often be configured beyond their default settings with this field. The parameters that can be used depend on the type of the FormElement. You can look up valid parameters for a specific FormElement in the official documentation.

Exercises

  1. Set the default value of the title field on the Book form to New Book.

  2. Set the default value of the number of pages field on the Book form to 100.

Subrecord

Let’s make use of the Value tab now and add a subrecord! What’s a subrecord? A subrecord, by our own definition, is a table within a form that contains multiple elements belonging to the primary record displayed in the form. These elements often have their own forms where they can be edited. In our subrecord we can display links to those forms to quickly edit the sub-elements.

We would like to add a subrecord to our Book form that lists every rental of the book (the person who has rented the book, start date, due date to return the book and the date when the book was returned). The following screenshot shows what our finished subrecord will look like:

Subrecord with rentals

First we create a few records in our database in the table Rental – otherwise our list of rentals would be empty. On our Book form we then create a new FormElement of the class native and the type subrecord. Look at the screenshots below to see how we configure the subrecord Form Element:

_images/subrecord_settings_1.png _images/subrecord_settings_2.png

sql1 Code

SELECT r.id AS '_id',
       CONCAT(p.firstName, ' ',  p.lastName) AS 'Person',
       DATE_FORMAT(r.start, '%d.%m.%Y') AS 'From',
       DATE_FORMAT(r.end, '%d.%m.%Y') AS 'Due',
       DATE_FORMAT(r.returned, '%d.%m.%Y') AS 'Returned'
  FROM Rental AS r, Person AS p
 WHERE r.bookId = {{id:R0}}
   AND r.personId = p.id

Notes:

  • In the first screenshot, notice that there is an additional option at the bottom called Subrecord Option. By checking all the boxes (new, edit, delete) we will automatically get buttons to create, edit and delete our rental records.

  • Notice how we chose myRental as the name of the FormElement. - The rentals are not stored in the same record as the book, so we cannot hoose a column name from our primary table (Book). - We prepend my to the name as a best practice, so that any developer can quickly see that the data in this FormElement is not from the primary table.

  • In the second screenshot, you can see that we can use the sql1 field to specify what data will be displayed in the subrecord. - By choosing custom column names we can set the column headers of the subrecord. - We need to include the id in our SELECT statement so that QFQ can build valid new/edit/delete-links. - To display only rentals of the book that is currently opened in the form, it is important that we use the condition:

    WHERE r.bookId = {{id:R0}}

  • In the parameter field we wrote:

    form=Rental

    All of the buttons on this subrecord (new, edit, delete) are now linked to a form with the name Rental. For example, if we click the edit button on a rental record, the Rental form should open up and load the data of the chosen rental record.

  • Something else you will most likely want to add to the parameter field is:

    detail=id:bookId

    This will pass the id of the subrecord’s form (in this case the Book form) to the form that is linked to the subrecord (in this case the Rental form). Not only is the id passed on, but it is mapped to the field bookId on the new form. This means that the Rental form will have the correct book preselected when opened via our subrecord!

Exercise 1

  1. Create the subrecord for rentals on your Book form as seen in this chapter.

  2. Create another subrecord for rentals on your Person form. This time the subrecord should list all the books the person has rented.

  3. Create another subrecord on the Person form, call it authoredBooks. It should show all the books the person has written.

Container FormElements

Our subrecord for the rentals seems a bit out of place where we are displaying it right now. It does not belong right next to very basic information about a book, like its title or author. Let’s divide the form into two tabs – one for general information and the second for rentals.

To do this, we create two FormElements, both of class container and type pill:

_images/general_pill.png _images/rental_pill.png

Now we want to place all of our other FormElements in one of the pills. Let’s rearrange the order of our FormElements, so that the pills are above the elements they should contain:

_images/pills_order.png

All the other FormElements are now displayed with a red background, because these elements are not yet assigned to a container. In the Basic tab of all these FormElements, we can now assign them to the pills:

_images/assign_pill.png

Once we have done that for all of our FormElements, here’s the result:

_images/pills_done.png

Pills are the container we use the most. But there are also two other container types:

  • fieldset – can be used to further group elements within a pill (a normal HTML fieldset).

  • template group – is used to group a few elements together and repeat them x times. The user can create a new set of these elements by clicking a + button and remove any entry by clicking the - button next to it. Template groups are similar to subrecords since they are used to manage sub-records of the primary record on a form.

Exercise 1

  1. Create the General pill and the Rental pill on your Book form and assign all FormElements to their pill.

  2. Create a General, Rental and Authored Books pill on the Person form. Assign the subrecords to their corresponding pill and the rest of the Form Elements to the General pill.

Dynamic Update and the Form-Store

In a previous exercise, we made this form to create and edit Rental records.

Rental Form

Let’s make a little quality of life upgrade for the librarian. Suppose books can be rented only for one month at a time. If a librarian manually creates a new rental, he or she will always have to type both start and due date.

To make life easier, we could automatically fill in the due date when the start date is entered. But how can we change the value of the due date input whenever the start date input changes?

Without QFQ, we would most likely solve this with a few lines of JavaScript. In QFQ it is even simpler than that: what we are looking for is called Dynamic Update.

Dynamic Update is a setting that can be enabled or disabled on any FormElement. By default it is disabled, but we need to enable it on the start FormElement and on the due FormElement. Simply check the box in the Basic tab of the FormElement:

Dynamic Update on start field Dynamic Update on end field

What does it do though? If the value of any FormElement with Dynamic Update changes, then all FormElements with Dynamic Update will be refreshed. Refreshed in this case means: all of the fields you can configure for the FormElement are recalculated.

Since we want to automatically set the value of the due date, let’s add this to the value field of our due date element:

{{
SELECT IF('{{end:RE}}' = '',
          DATE_FORMAT(DATE_ADD('{{start:RE}}', INTERVAL 1 MONTH), '%Y-%m-%d'),
          '{{end:RE}}')
}}

In this query, we check if there is already a due date entered. If there is one, we keep it – otherwise our code might accidentally overwrite a date the user has set manually. If there is no due date yet, we set it to start date + 1 month. In MySQL this is done with DATE_ADD and INTERVAL. We also have to use DATE_FORMAT so that the output matches the expected formatting of the date field.

However, recalculating the fields has no visible effect yet, because the result is still the same. We are missing one piece of the puzzle. Compare the above code to this one:

{{
SELECT IF('{{end:FRE:alnumx}}' = '',
          DATE_FORMAT(DATE_ADD('{{start:FRE:alnumx}}', INTERVAL 1 MONTH), '%Y-%m-%d'),
          '{{end:FRE:alnumx}}')
}}

The difference is that we are now also using the Form Store.

  • The Record Store within a form only contains values that are already saved in the database. Record Store variables therefore only change after saving again – that’s the issue here.

  • The Form Store, however, contains the values of all FormElements on the form. These values are updated immediately when a Dynamic Update happens!

  • Just like the Client Store, the Form Store by default tries to cast your variable into a digit, which is why we use the sanitize class alnumx.

  • When we specify FRE as the store of our variable, QFQ will first try to get it from the Form Store, then from the Record Store, and if neither exists, it returns an empty string.

Exercise 1

  1. Modify the Author dropdown on your Book form. Use the Note field of the FormElement (Layout tab) to display all other books this author has written (e.g. Author of: The Hobbit, …). This text must update dynamically when another author is selected from the dropdown!

  2. In the Rental form, add a FormElement of type note that says Overdue!. This info should only be displayed if: - the book has not been returned yet (returned = 0), and - the current date is past the due date.

    Use the mode sql field in the Basic tab of your FormElement to show or hide it! - If your SQL returns show, the element will appear. - If it returns hidden, it will not.

    Furthermore, this info must update dynamically as the user edits the form values.

Action FormElements

There is a third class of FormElements we haven’t talked about: Action FormElements. These elements execute an action when a specified event happens. Most of them already have their trigger in the name:

  • beforeLoad / afterLoad – execute before/after the form loads

  • beforeSave / afterSave – execute before/after the form is saved (both on INSERT and UPDATE)

  • beforeInsert / afterInsert – execute before/after the form saves a new record (r=0 → INSERT)

  • beforeUpdate / afterUpdate – execute before/after the form updates an existing record (r!=0 → UPDATE)

  • beforeDelete / afterDelete – execute before/after the record in the form is deleted

But how do we specify what exactly happens when one of these events is triggered?

Example: When a Book is deleted from the database, Rental records may remain that still reference this book. We want to add a beforeDelete action to the Book form that deletes all Rental records linked to the deleted book.

For this we can use parameters that contain the actual action. The simplest one is sqlAfter.

The name is confusing because it says “after”. But since we already defined that the action belongs to beforeDelete, QFQ knows when to execute it. If there are multiple actions of the same class (e.g. multiple beforeDelete elements), sqlBefore runs before sqlAfter. If you use only sqlAfter, then execution order is determined solely by the Order field of the Action Elements.

The sql in sqlAfter means that we are going to execute SQL when the action is triggered. Here is the configuration of our beforeDelete element:

Action FormElement beforeDelete (settings 1) Action FormElement beforeDelete (settings 2)

Now, if we delete a book, all Rental records referencing this book are also deleted.

Alternative solutions:

  • Configure CASCADE rules in the MySQL database. This requires proper foreign keys.

  • Instead of deleting, many applications just mark a record as “deleted” (soft delete). That way no references are broken, since the data still exists.

Exercise 1

  1. Add the beforeDelete element from this chapter to your Book form.

  2. Add another beforeDelete element to the Person form that deletes all Rentals of this person.

  3. On the Book form, it is currently possible to enter a negative number of pages. Use an Action FormElement to validate user input when the form is saved and display an error message if the number is invalid.

Instead of one parameter (sqlAfter), you will need three in this case: sqlValidate, expectRecords, messageFail. Check the documentation to see exactly how an Action FormElement can be used this way: http://docs.qfq.io/master/Form.html?#formelement-parameter-sqlvalidate

Tip: sqlValidate, expectRecords and messageFail all go into the Form.Parameter. You might need to use {{! ... }} for the sqlValidate parameter.

Example:

sqlValidate = <QUERY>
expectRecords = <NUMBER>
messageFail  = "FAIL"

Using Report-Syntax inside a Form

There are (very few) cases, in which normal QFQ FormElements are not able to achieve exactly what we want. Luckily, we have the option to write in QFQ-Report Syntax within a Form! This is a hidden, but very useful feature - because anything is possible in a Report!

No actual use case is shown here, because these are often the most complicated of cases and they have no place in this tutorial. We are just going to show how it is done. On our Book Form in the rental tab we will add a FormElement with type note. Usually it is used to display a small text or a little bit of HTML. We configured it like this:

  • Name: reportAvailable

  • Type: note

  • BS Label Columns: 0

  • BS Input Columns: 12

  • BS Note Columns: 0

  • Value:

    _images/form_report_value.png

The result is the following label, which states whether the book is currently available or not:

_images/form_report_result.png

The takeaway from this is: You can use the power of the Report syntax in a Form by starting with #!report. However, you should only use this when needed, because things get messy when this is overused!

Exercise 1

  1. Add a Note FormElement to any of your forms and use it to write Report Syntax. It doesn’t matter what your report does - go wild!

Bonus Exercises

Solution

The solutions for Bonus & Advanced are both under Advanced. Depending on QFQ version there might be slight differences in style.

Typo3 Backend: https://webwork22.math.uzh.ch/qfqtutorial/typo3 | Frontend: https://webwork22.math.uzh.ch/qfqtutorial

Exercise 1

  1. When we create a new ‘Rental’ via subrecord on the Book-Form, then we would like the Book to be preselected on the Rental-Form that opens.

    • Study the ‘FormElement.parameter’ section of http://docs.qfq.io/master/Form.html#type-subrecord and find out how we can pass the book’s id from the Book Form to the Rental Form

    • Pass the person’s id instead, if the Rental Form is opened via subrecord on the Person Form.

    • _images/rental_preselect_book.png

  2. Create a Form BookGenre. The Form should have a ‘readonly’ (FormElement.mode) dropdown that has a preselected Book and a dropdown that contains a list of all Genres.

    _images/add_new_genre_to_book.png

  3. Create a ‘Genres’ subrecord on the Book Form. When a Genre is added, the correct Book needs to be preselected on the BookGenre Form!

    _images/book_subrecord_of_genre.png

  4. Create a beforeSave action FormElement on the BookGenre Form that stops the user from adding the same genre to a book twice!

  5. Create a Page “Rentals”.

    • Create a Summary section at the top of this page. Show the following statistics:

      • Number of books rented (all time)

      • Number of books currently rented

      • Number of books overdue and not returned

      • Top 3 most rented books

      • Top 3 people who rented most books

    _images/summary_result_example.png

    • Below the Summary, create a table with all Rentals. Show person name, book title, start, due and returned dates.

    • The whole row should have a green/success background color, if the book has already been returned. If the book is overdue, the row’s backgroundcolor should be red.

    • Order the whole table so that the Rentals with the earliest due dates appear at the top.

    • Make a button above the table that allows you to hide all Rentals that have already been returned. If the user is currently hiding these records, the button should say ‘Show returned Rentals’ instead.

      _images/summary_table_example.png

  6. Create a Page “Settings” in the Admin-area. Create a report on this page that shows a table with all Grp-records with grId=0 (Show Edit-Button, Name, Reference and number of sub-elements). Also create a ‘new’ button.

    _images/settings_report_example.png

  7. Create a Form to edit Grp-records (grId, name, reference, subrecord for all child-Grps).

  8. Add an upload Form-Element to your Book Form, where the cover-image of the book can be uploaded.

    • Study the upload element in the official documentation (Upload Simple mode): http://docs.qfq.io/master/Form.html#type-upload

    • You will be saving the image path in the column coverPathFileName of the Book Table in your database. The image itself will be saved somewhere in the ‘fileadmin’ folder.

    Display the uploaded cover image in two places:

Advanced

These things are not part of the tutorial, but are good to know!

Small Project

Requirements

You can choose a generic example, e.g. students with lectures. A task description should then be formulated that requires the following features:

  • Create / Edit / Delete records

  • Subrecord or Template Group

  • File Upload (pdf)

  • Email sending

  • Download (pdf)

Concept

  • Create a mockup, e.g. with draw.io, balsamiq or by hand…

  • Create a database schema

Implementation

The learner should implement the self-created concept as independently as possible after consultation with the supervisor. For specific problems (e.g. file upload, subrecord, email sending …) links to the relevant sections in the QFQ documentation will be provided.

Testing

Test cases will be derived from the requirements. A test protocol should be created and afterwards the tests should be carried out.

Typo3 Basic Setup

Install Extensions QFQ and Typo3: - https://qfq.io/download/?dir=snapshots (Take actual master or develop version) - https://www.math.uzh.ch/repo/?dir=uzhcd/UZH_CD_2023 (Take actual version)

_images/tutorial-report-installedExtensions.png

Create a new Home page and define UZH CD template:

_images/tutorial-report-newSubpage.png _images/tutorial-report-templateExtension.png _images/tutorial-report-editTemplateOptions.png _images/tutorial-report-editTemplate.png _images/tutorial-report-editPage.png _images/tutorial-report-editPageAppearance.png

Create this qfq tt-content in your Home page:

# Link for qfq tutorial

# Title
{
  sql = SELECT '<h2>QFQ Dev Environment</h2>'
}

# Todo info
{
  sql = SELECT '- Execute the exercises from the tutorial links below in your qfq dev environment'
             , 'Feel free to create your own new subpages and qfq reports on backend side: <a class="btn btn-default" href="{{baseUrl:Y}}typo3" target="_blank">T3 Backend</a>'
             , 'You need new tables/columns or changes in your database? Here we are: <a class="btn btn-default" href="http://webwork20:<db-port>" target="_blank">Database</a>'
             , 'QFQ Framework Documentation: <a class="btn btn-default" href="http://docs.qfq.io/master/" target="_blank">QFQ Doc</a>'
  head = <h3>Todo:</h3>
  fend = <br>
}

# Tutorial link info
{
  sql = SELECT '<a class="btn btn-default" href="https://project.math.uzh.ch/projects/praktikum/wiki/QFQ_Introduction#Vorkenntnisse-SQL" target="_blank">SQL Basics</a>'
             , '<a class="btn btn-default" href="https://project.math.uzh.ch/projects/praktikum/wiki/QFQ_Introduction#Vorkenntnisse-Web-Entwicklung" target="_blank">HTML/CSS/JS Basics</a>'
             , '<a class="btn btn-default" href="https://project.math.uzh.ch/projects/praktikum/wiki/QFQ_Introduction#QFQ" target="_blank">QFQ</a>'
  head = <h3>Tutorial Links:</h3>
  fbeg = <br>
}

# Credentials and url info
{
  sql = SELECT 'QFQ_DEV_INFO.txt'
  head = <h3>Hints:</h3>Your initial credentials and the urls for frontend/backend/database can be found in file
}