Eigenes SQL
Die ersten Hinweise für die Möglichkeiten der Filterregel
„Eigenes SQL“ sind über die 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 oder mit dem Tabellennamen bzw. dessen Alias versehen werden (siehe MySQL Identifier). Damit ist die Verwendung auch von in (My)SQL reservierten Wörter möglich.
Bei komplexeren Queries ist es ratsam, diese vor dem Einbau mit entsprechenden SQL-Tools wie phpMyAdmin, PHPStorm o. ä. zu testen bzw. bei Verschachtelungen Stück für Stück aufzubauen und vorab mit festen Werten zu arbeiten. Die entsprechenden Daten sollten dann natürlich auch als Items in der DB vorhanden sein. Als letzten Schritt fügt man ggf. notwendige dynamische Parameter mit den zur Verfügung stehenden Inserttags hinzu. Die MM-SQL-Inserttags werden nur innerhalb der Verarbeitung des Query aufgelöst und stehen daher auch nicht allgemein im FE zur Verfügung.
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).“
1SELECT `id`
2FROM {{table}}
3WHERE `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“
1SELECT `id`
2FROM {{table}}
3WHERE FROM_UNIXTIME(`date_start`) >= CURDATE()
oder
1SELECT `id`
2FROM {{table}}
3WHERE 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)“
1SELECT `id`
2FROM {{table}}
3WHERE
4( DATE(FROM_UNIXTIME(`date_start`)) >= DATE(NOW()) )
5OR
6( DATE(FROM_UNIXTIME(`date_start`)) <= DATE(NOW())
7 AND
8 DATE(FROM_UNIXTIME(`date_end`)) >= DATE(NOW())
9)
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“]
1SELECT `id`
2FROM {{table}}
3WHERE (`date_start` IS NULL OR `date_start` = '' OR `date_start` < UNIX_TIMESTAMP())
4AND (`date_stop` IS NULL OR `date_stop` = '' OR `date_stop` > UNIX_TIMESTAMP())
Alternativ
1SELECT `id` FROM {{table}}
2WHERE (`date_start` IS NULL OR DATE(FROM_UNIXTIME(`date_start`)) <= DATE(now()))
3AND (`date_stop` IS NULL OR DATE(FROM_UNIXTIME(`date_stop`)) >= DATE(now()))
Filterung nach Datum (start) und Veröffentlichungsdatum mit Prüfung per GET
Zum Beispiel für Events, die nach Erreichen des Startdatums ausgeblendet werden sollen aber erst ab einem bestimmten Datum angezeigt werden dürfen - sofern gesetzt.
Zur Prüfung kann im FE an die URL ein GET-Parameter angehangen werden - Datumsformat ist „YYYY-MM-DD“ z. B. „domain.tld/meine-liste.html?now=2023-07-10“.
1SELECT id FROM {{table}}
2WHERE DATE(FROM_UNIXTIME(`date_start`)) >= DATE(now())
3AND (`date_published` IS NULL
4 OR DATE(FROM_UNIXTIME(`date_published`)) <= DATE(now())
5 OR DATE(FROM_UNIXTIME(`date_published`)) <= {{param::get?name=now}}
6)
Filterung nach Datum (start) letzen 12 Monate
Archivfilter für vergangene Items der letzten 12 Monate:
1SELECT id FROM {{table}}
2WHERE DATE(FROM_UNIXTIME(`date_start`)) < DATE(now())
3AND DATE(FROM_UNIXTIME(`date_start`)) >= DATE(now() - INTERVAL 12 month)
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.“
1SELECT `id`
2FROM mm_child
3WHERE `pid` = (
4 SELECT `id`
5 FROM mm_parent
6 WHERE
7 `parent_alias` = {{param::get?name=auto_item}}
8 LIMIT 1
9)
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.“
1SELECT `id`
2FROM mm_parent
3WHERE `id` = (
4 SELECT `pid`
5 FROM mm_child
6 WHERE
7 `child_alias` = {{param::get?name=auto_item}}
8 LIMIT 1
9)
oder kürzer
1SELECT `pid` as id
2FROM mm_child
3WHERE `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 - sofern diese Liste mit Items zurück liefert - wird die „Basismenge“ und die Reihenfolge der Items festgelegt und in den weiteren Regeln wird diese Menge nur noch gekürzt. Die Sortierrichtung ist bei MySQL immer ASC - möchte man eine andere Richtung, dann die Angabe bei jeder angegebenen Sortierspalte mit angeben.
1SELECT `id`
2FROM mm_mannschaft
3ORDER BY `punkte` DESC, `spiele` ASC, `prio` DESC
Sortierungen der Ausgabe nach einer Nummer und NULL-Werten oder Zufall
Zu beachten ist, dass diese SQL-Regel im Filter als erste Regel eingebaut wird. Anzeige der Items nach einer eigenen Sortierungsnummer aber alle Items ohne Nummer (NULL) ans Ende:
1SELECT `id`
2FROM mm_sv_categories
3ORDER BY ISNULL(`sort_number`), `sort_number` ASC
Man kann auch einzelne Items als Erste anzeigen lassen (Attribut „Prio-Slider“ = 1) und den Rest per Zufall:
1SELECT `id`
2FROM mm_sv_trainings
3ORDER BY `prio_slider` DESC, rand()
Sortierung der Ausgabe referenziertem MM und Name
Hat man z. B. ein MM Produkte, in denen jeweils ein Partner per Einfachauswahl [select] referenziert ist und man möchte die Produkte so ausgeben, dass diese erst nach der manuellen Sortierung (sorting) der Partner sortiert ist und anschließend nach dem eigentlichen Produktnamen, kann man das mit den folgenden Code erreichen:
1SELECT pro.id FROM mm_products AS pro
2LEFT JOIN mm_partners AS part ON pro.partner = part.id
3WHERE pro.published = 1
4ORDER BY part.sorting, pro.product_code
In der Ausgabeliste könnte man damit z. B. bei jedem neuen Partner eine Zwischenüberschrift ausgeben. Dazu die aktuelle Partner.ID in einer temp. Variable abspeichern und in jedem Schleifendurchgang auf Gleichheit prüfen - wenn Ungleich, dann Ausgabe „Partnername“.
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
1SELECT `id` FROM mm_monate
2WHERE FROM_UNIXTIME(`von_datum`) <= IF(
3 {{param::get?name=von_datum}},
4 {{param::get?name=von_datum}},
5 CURDATE()
6)
7ORDER 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.
1SELECT `id` FROM mm_mitarbeiter
2WHERE `driver_licence` = IF(
3 {{param::get?name=driver_licence}},
4 {{param::get?name=driver_licence}},
5 ''
6)
Übergabe mehrerer Werte für IN()
Mehrere Werte können an das Query als kommaseparierte Liste oder Array übergeben werden - je nach Typ der Übergabe gibt es für den Parameter aggregate den Wert list oder set.
1-- als Liste
2-- domain.tld/de/liste?id=13,15,19
3SELECT id FROM {{table}}
4WHERE id IN ({{param::get?name=id&aggregate=list}})
5
6-- als Array
7-- domain.tld/de/liste?id[]=13&id[]=15&id[]=19
8SELECT id FROM {{table}}
9WHERE id IN ({{param::get?name=id&aggregate=set}})
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.
1SELECT `id` FROM mm_mitarbeiter
2WHERE `abteilung` IN (
3 SELECT `id` FROM mm_abteilung
4 WHERE `score` > 99
5)
oder
1SELECT ma.id FROM mm_mitarbeiter ma
2LEFT JOIN mm_abteilung rel ON (ma.abteilung = rel.id)
3WHERE rel.score > 99
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_member“ haben aber die Eingrenzung, dass ein Mitglied nur einmal ausgewählt werden darf, dann folgenden String einsetzen:
1(SELECT tl_member.id FROM tl_member
2 LEFT JOIN mm_member
3 ON mm_member.memberId=tl_member.id
4 WHERE
5 mm_member.memberId IS NULL
6 AND
7 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-- URL: ....&id=mm_mitarbeiter::51&...
2SELECT * FROM mm_mitarbeiter
3WHERE `id` = SUBSTRING_INDEX({{param::get?name=id}},'::',-1)
Filter für Mehrfachauswahl in der Eingabemaske: nur unausgewählte Items
Hat man z. B. eine Tabelle Regionen und dort eine Mehrfachauswahl auf Länder und möchte die Auswahl auf die Länder begrenzen, die noch nicht zugewiesen wurden, kann man bei dem Attribut Mehrfachauswahl (ID: 42) auf die Länder einen Filter aktivieren. In dem Filter kann man eine Filterregel „Eigenes SQL“ wie folgt anlegen:
1SELECT `id`
2FROM mm_countries
3WHERE `id` NOT IN (
4 SELECT `value_id` as id
5 FROM tl_metamodel_tag_relation
6 WHERE `att_id` = '42'
7) OR id IN (
8 SELECT `value_id` as id
9 FROM tl_metamodel_tag_relation
10 WHERE `att_id` = '42'
11 AND `item_id` = SUBSTRING_INDEX({{param::get?name=id}},'::',-1)
12)
Filterunterscheidung von Frontend und Backend
Bei den Filterungen mit eigenem SQL kann es notwendig sein, eine Unterscheidung zwischen Frontend und Backend zu erreichen. Seit MM 2.2 werden die beim Attribut Select und Tags eingestellten Filter auch im Frontend angewendet, so dass es Problemen mit Filterregeln kommen kann, die nur in der Eingabemaske zum Tragen kommen sollen.
Man kann eine Abfrage auf den aktuellen Request-String setzen und dort nach dem eigenen Modelnamen wie z. B. „mm_employees“ als erstes Wort suchen.
1SELECT artd.id FROM mm_article_details artd
2LEFT JOIN tl_metamodel_tag_relation rel ON (artd.id = rel.item_id)
3WHERE
4IF (SUBSTRING_INDEX(SUBSTRING_INDEX('{{env::request}}', '/', -1), '?', 1) = 'mm_employees',
5 rel.att_id = 43 AND -- 43 ID des Attributes [tags]
6 rel.value_id = SUBSTRING_INDEX({{param::get?name=id}},'::',-1), -- variable ID aus URL für Artikel/Produkt
7 1=1
8)
Bemerkung
Mit MM-Version 2.3-beta1 hat sich das Routing im BE geändert - statt domain.de/contao?do=metamodel_mm_employees&act=edit...
kommt nun ein domain.de/contao/metamodel/mm_employees?act=edit
, d. h. vor der Änderung wurde bei der Abfrage
SUBSTRING_INDEX(SUBSTRING_INDEX('{{env::request}}', '/', -1), '?', 1)
der Wert „contao“ geliefert.
Bemerkung
In MM 2.3 wurde der Filterregel eine Auswahl hinzugefügt, mit der die Ausführungsumgebung wie z. B. „Nur im Backende“ bestimmt werden kann. Damit können die SQL-Queries vereinfacht werden.
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: