Feature #17319
open
Report Notation 2.1: 'if then else' / 'for' / 'while'
Added by Carsten Rose 8 months ago.
Updated about 2 months ago.
Description
- Es kommt regelmaessig vor das ein SELECT Statement nur gefeuert wird weil entschieden werden soll ob in der Applikationslogik die Nested Queries ausgefuehrt werden sollen oder nicht. Dafuer waere ein simples 'if/then/else' effizienter das nicht via SQL sondern via PHP entschieden wird.
- Oder es soll über zwei Tabellen
SELECT 'vorlesung' AS _tbl UNION SELECT 'seminar'
oder 20 Records iteriert werden. Eine Schleifen Logik waere gut.
- Im FormEditor > FormElement modeSql koennte if/then/else zur Bestimmung von show/readonnly/hidden vewendet werden.
- Related to Feature #8975: Report Notation: 2.0 - `10.sql` wird ersetzt durch `<name>.sql` resp `<name> { ...` - Merge Request added
IMHO we should not extend the report syntax with loops in a standard way, but keep it in the QFQ-style: use nesting to iterate over a table/data source generated dynamically. Think of the pythonian way of a for loop:
for i in range(0,10):
...
For instance, we can declare for-loops as
{
head=<ul>
tail=</ul>
lql=SELECT index, value FROM Range(0,10,2)
{
lql=SELECT '{{index:R}}: Random item {{value:R}} is {{random:V}}' | '_+li'
}
}
lql
stands for "local query language" that is evaluated locally by PHP - this is only an ad hoc name for this example and the actual token name to be used is left for a discussion.
- The data source
Range(start,end,step)
represents a source of rows with two columns:
value
runs from start
(inclusive) to end
(exclusive) and increased by step
at each iteration (default: 1)
index
starts from 0 (or 1?) and is increased by one at each iteration.
Other examples of data sources suitable for iteration:
- a collection of records selected in the last sql query (say we want to iterate twice over the records, but fetch them only once). This requires a syntax to temporarily save all fetched rows (or at least some columns from them), so that they can be reused
- messages received on a JavaScript Websocket (once implemented) - in this case the loop is evaluated by JavaScript and not PHP
- ???
while
-loops can be done the same way with a data source that returns rows while some condition is satisfied.
I am not sure if SQLite will help - lql
does not select data from actual tables.
In order to avoid the "slow php parser", the source code of a report can be transpiled into a more optimized code with all those statements already parsed. Maybe some template engines can do this already?
For the if-then-else, one approach would be to implement a switch pattern with nested blocks.
{
# Get a value to be tested. If more than one columns is returned,
# the first will be used in shortcut notation
sql=SELECT dayOfWeek FROM meetings
{
case={{dayOfWeek}} IS 'Mon' # full syntax
case=Mon # shortcut
sql=SELECT "I hate Mondays"
}
{
case={{dayOfWeek}} IN ('Sat', 'Sun') # full syntax
case=Sat,Sun # shortcut
sql=SELECT "Today I'm not gonna do aaaanything"
}
{
case=default # Runs only if none of other case-blocks is a match
sql=SELECT "OK, I'll check the emails"
}
{
sql=SELECT "This runs everytime as usual"
}
}
Notes:
- Use
case="default"
for a block to be run only if the tested value is the string default
- Use
case=true
and case=false
for true/false tests
- Related to Feature #5345: Report: UPDATE / INSERT / DELETE statements should trigger subqueries, depending on the result. added
- Related to Feature #17441: Report Notation 2.2: fireIf :Statement to trigger the root level query / fireSubIf : Statement to trigger the subquery added
- Tracker changed from Support to Feature
- Description updated (diff)
Also available in: Atom
PDF