Eigenes SQL

Die ersten Hinweise für die Möglichkeiten der Filterregel „Eigenes SQL“ sind über die img_about Hilfe zu finden.

Nochmal der Hinweis: Auch mit der Filterregel „Eigenes SQL“ werden nur IDs zur nächsten Filterregel bzw. zum Filterset weiter gereicht. Es können keine „Attributwerte“ hinzugefügt oder berechnet werden, auch wenn das per SQL z.B. durch JOINs oder mathematische Anweisungen möglich wäre.

Spaltennamen sollten immer in Backticks ` wie z.B. `name` gesetzt werden (siehe MySQL Identifier). Damit ist die Verwendung auch von in (My)SQL reservierten Wörter möglich.

Folgend einige SQL-Queries als „Zutat“ für das eigene „SQL-Menü“:

„LIKE“-Abfrage mit Defaultwert

„Suche Items für die Attribut ‚name‘ wenn GET-Parameter ‚name‘ gesetzt ist oder gebe alle Items aus (keine Filterung).“

1
2
3
SELECT `id`
FROM {{table}}
WHERE `name` LIKE (CONCAT('%',{{param::get?name=name&default=%%}},'%'))

Filterung nach Datum

„Suche Items für die Attribut ‚date_start‘ größer oder gleich dem heutigen Datum ist - also in der Zukunft liegt“

1
2
3
SELECT `id`
FROM {{table}}
WHERE FROM_UNIXTIME(`date_start`) >= CURDATE()

oder

1
2
3
SELECT `id`
FROM {{table}}
WHERE DATE(FROM_UNIXTIME(`date_start`)) >= DATE(now())

Filterung nach Datum (start oder „laufend“)

„Suche Items für die Attribut ‚date_start‘ größer oder gleich dem heutigen Datum ist - also in der Zukunft liegt - oder die Items bei denen das aktuelle Datum zwischen ‚date_start‘ und ‚date_end‘ liegt (laufend)“

1
2
3
4
5
6
7
8
9
SELECT `id`
FROM {{table}}
WHERE
( DATE(FROM_UNIXTIME(`date_start`)) >= DATE(NOW()) )
OR
( DATE(FROM_UNIXTIME(`date_start`)) <= DATE(NOW())
  AND
  DATE(FROM_UNIXTIME(`date_end`)) >= DATE(NOW())
)

Filterung nach Datum (start/stop)

„Suche Items für die das Attribut ‚start‘ größer dem aktuellen Unix-Zeitstempel ist und das Attribut ‚stop‘ noch nicht erreicht ist. Leere Attributwerte werden als nicht relevant umgesetzt (dann nur ‚start‘ bzw. ‚stop‘ relevant).“ [von „Cyberlussi“]

1
2
3
4
5
6
7
8
SELECT `id`
FROM {{table}}
WHERE (
  `start` IS NULL OR `start` = ''
  OR `start` < UNIX_TIMESTAMP())
  AND (`stop` IS NULL OR `stop` = ''
  OR `stop` > UNIX_TIMESTAMP()
)

Filterung nach Kind-Elementen eines Eltern-Elements

„Suche alle Kind-Elemente für ein gegebens Eltern-Element über den Alias-Parameter - z.B. um auf einer Detailseite alle zugehörigen ‚Kind-Elemente‘ auszugeben.“

1
2
3
4
5
6
7
8
SELECT `id`
FROM mm_child
WHERE `pid` = (
  SELECT `id`
  FROM mm_parent
  WHERE
  `parent_alias` = {{param::get?name=auto_item}}
)

Filterung nach Eltern-Element eines Kind-Elements

„Suche das Eltern-Element für ein gegebens Kind-Element über den Alias-Parameter - z.B. um auf einer Detailseite das zugehörige ‚Eltern-Element‘ auszugeben.“

1
2
3
4
5
6
7
8
SELECT `id`
FROM mm_parent
WHERE `id` = (
  SELECT `pid`
  FROM mm_child
  WHERE
  `child_alias` = {{param::get?name=auto_item}}
)

oder kürzer

1
2
3
SELECT `pid` as id
FROM mm_child
WHERE `child_alias` = {{param::get?name=auto_item}}

Sortierung der Ausgabe nach mehr als einem Attribut (fest)

„Sortiere ‚Mannschaften‘ nach Punkte absteigend + Spiele aufsteigend + Priorität absteigend.“ siehe auch Forum

Zu beachten ist, dass diese SQL-Regel im Filter als erste Regel eingebaut wird. In der ersten Regel wird die „Basismenge“ und die Reihenfolge der Items festgelegt und in den weiteren Regeln wird diese Menge nur noch gekürzt.

1
2
3
SELECT `id`
FROM mm_mannschaft
ORDER BY `punkte` DESC, `spiele` ASC, `prio` DESC

Dynamischer Defaultwert

Bei dem eigenen SQL sind Defaultwerte per ‚default=<wert>‘ möglich, die verwendet werden, wenn der Filterparameter nicht gesetzt ist. Im Param-Tag ist aktuell noch keine Verschachtelung von Insert-Tags oder der Einsatz von MySQL-Funktionen möglich, so dass man bei dynamischen Defaultwerten auf einen Workaround per „SQL-IF“ zurückgreifen muss. siehe auch Github #880

1
2
3
4
5
6
7
SELECT `id` FROM mm_monate
WHERE FROM_UNIXTIME(`von_datum`) <= IF(
   {{param::get?name=von_datum}},
   {{param::get?name=von_datum}},
   CURDATE()
)
ORDER BY `von_datum` DESC

Defaultwert ‚‘

Bei dem eigenen SQL sind Defaultwerte per ‚default=<wert>‘ möglich, die verwendet werden, wenn der Filterparameter nicht gesetzt ist. Im Param-Tag ist aktuell wird akltuell die Eingabe von ‚‘ oder „“ gecastet, so dass die Filterung nicht korrekt erfolgt; anzuwenden ist dies z.B. bei Checkboxwerten.

1
2
3
4
5
6
SELECT `id` FROM mm_mitarbeiter
WHERE `driver_licence` = IF(
   {{param::get?name=driver_licence}},
   {{param::get?name=driver_licence}},
   ''
)

Tags für ein Item filtern

Die Mitarbeiter haben eine Mehrfachauswahl [tags] zum MetaModels „Softskills“. Für die Detailansicht eines Mitarbeiters, sollen diese ermittelt werden - die Detailansicht wird über das „auto_item“ per Alias gefiltert.

Die Softskills werden als eigene Liste auf der Detailseite angezeigt, müssen aber entsprechend gefiltert werden. Für die Ermittlung der Daten, muss man über die Relationstabelle „tl_metamodel_tag_relation“ gehen. Wichtig ist die Ermittlung der Attribut-ID für „rel.att_id“, d.h. in den Attributen von „Mitarbeitern“ hat die Mehrfachauswahl z.B. die ID 5 (zu ermitteln über den i-Button).

1
2
3
4
SELECT DISTINCT(rel.value_id) as id FROM mm_mitarbeiter as ma
LEFT JOIN tl_metamodel_tag_relation rel ON (ma.id = rel.item_id AND rel.att_id=5)
WHERE
ma.alias = {{param::get?name=auto_item}}

Items nach Einfachauswahl-Eigenschaft filtern

Die Mitarbeiter haben eine Einfachauswahl zum MetaModels „Abteilung“. Für eine Listnsicht der Mitarbeiter, sollen nur diejenigen ausgegeben werden, die in einer Abteilung arbeiten deren „Score“ größer als 99 ist.

1
2
3
4
5
SELECT `id` FROM mm_mitarbeiter
WHERE `abteilung` IN (
   SELECT `id` FROM mm_abteilung
   WHERE `score` > 99
)

oder

1
2
3
SELECT ma.id FROM mm_mitarbeiter ma
LEFT JOIN mm_abteilung rel ON (ma.abteilung = rel.id)
WHERE rel.score > 99

Mitarbeiter für eine per Mehrfachauswahl [tags] zugeordnete Seite filtern

Die Mitarbeiter haben ein Attribut Mehrfachauswahl auf die Tabelle tl_page, um auf einzelnen Seiten einen Mitarbeiter als Verantwortlichen darzustellen. Auf den entsprechenden Seiten kann ein MM-Listenelement eingefügt werden, der die zugehörigen Mitarbeiter ausgibt. Für die Filterung kann das folgende Query verwendet werden:

1
2
3
4
5
6
7
SELECT ma.id FROM mm_mitarbeiter ma
LEFT JOIN tl_metamodel_tag_relation rel ON (ma.id = rel.item_id)
WHERE
rel.att_id = 79 AND             -- 79 ID des Attributes [tags]
rel.value_id = {{page::id}} AND -- variable Seiten-ID
ma.published = 1
ORDER BY ma.name

Filterung einer Select-Auswahl im BE für eine nicht-MM-Tabelle

Hat man für das Attribut Einzelauswahl [select] eine Tabelle ausgewählt, die keine MM-Tabelle ist, steht als Filtermöglichkeit die Eingabe einer „WHERE-Eingrenzung“ zur Verfügung. Möchte man z.B. bei seinem Datensatz eine Verbindung zur Mitglieder-Tabelle „tl_members“ haben aber die Eingrenzung, dass ein Mitglied nur einmal ausgewählt werden darf, dann folgenden String einsetzen:

1
2
3
4
5
6
7
(SELECT tl_member.id FROM tl_member
 LEFT JOIN mm_member
        ON mm_member.memberId=tl_member.id
   WHERE
         mm_member.memberId IS NULL
   AND
         tl_member.id=sourceTable.id)

ID aus GET-Parameter nach ‚::‘ abtrennen

Bei Filterungen im Backend oder für das Frontend-Editing benötigt man ggf. Zugriff auf die ID aus dem GET-Parameter der URL. Dieser ist aber mit ‚::‘ an einen Tabellennamen gekoppelt und muss für die Verwendung in einem eigenen SQL-Query separiert werden. Das erfolgt z.B. über den den Befehl SUBSTRING_INDEX im Query, wie das folgende Beispiel zeigt:

1
2
3
-- URL: ....&id=mm_mitarbeiter::51&...
SELECT * FROM mm_mitarbeiter
WHERE `id` = SUBSTRING_INDEX({{param::get?name=id}},'::',-1)

Filter für ein Select/Tags in der Eingabemaske

Die Attribute Einfach- und Mehrfachauswahl (Select und Tags) können für die Eingabemaske mit einem Filter versehen werden. Soll dieser Filter dynamisch auf ein anderes Attribut reagieren, kann man mit der Filterregel „Eigenes SQL“ arbeiten und die dynamischen Parameter verwenden.

Als dynamischer Parameter kann z.B. die URL mit den GET-Parametern oder bei einem submitonchange eines Attributes in der Eingabemaske die POST-Parameter ausgewertet werden.

Zum Beispiel soll auf die Select-Auswahl der Abteilung die Liste der auswählbarer Mitarbeiter auf die eingeschränkt werden, die zur selben Abteilung gehören. „Gelauscht“ wird auf den POST-Parameter der Abteilung und anschließend kann mit QUERY-U (update) oder QUERY-C (create) die Mitarbeiterliste eingegrenzt werden.

1
2
3
4
SELECT `id` FROM  mm_mitarbeiter
WHERE IF (
      {{param::post?name=abteilung}} != 'NULL', (QUERY-U), (QUERY-C)
 )

Kommentare im SQL-Query

Die SQL-Queries können unter Umständen recht komplex werden und einige feste Werte wie Attribut-IDs usw. enthalten. Um für einen späteren Zeitpunkt oder die Arbeit im Team den Überblick nicht zu verlieren, können auch hier Kommentare eingefügt werden - mehr dazu im MySQL reference manual.

Beispiel: img_sql-comment