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 werden, auch wenn das per SQL z.B. durch JOINs möglich wäre.

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_startend`)) >= 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
 9
10
11
SELECT id
FROM {{table}}
WHERE (
  {{table}}.start IS NULL OR {{table}}.start = ''
  OR
  {{table}}.start<UNIX_TIMESTAMP())
  AND ({{table}}.stop IS NULL
  OR
  {{table}}.stop=''
  OR {{table}}.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 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)

Kommentare im SQL-Query

Die SQL-Queries können mit 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