OTRS Frage: Wie finde ich User, die einen bestimmten Eintrag in den Userpreferences nicht haben.
In OTRS werden in der Tabelle „users“ nur die wichtigsten Felder gespeichert, alles andere kommt in die Tabelle „user_preferences“ mit dem Schema: user_id, preferences_key, preferences_value
Im übrigen fehlt hier ein UNIQUE-Key für user_id und preferences_key, was bei uns tatsächlich bereits zu doppelten Einträgen geführt hat.
Nun also zur Aufgabe „Liste alle User, denen ein bestimmter Eintrag fehlt, z.B. „UserComment“
An und für sich hätten wir uns gedacht – Einfache Antwort: LEFT JOIN
Achtung falsch:
SELECT id, login, first_name, last_name, preferences_key, preferences_value FROM users
LEFT JOIN user_preferences
ON users.id = user_preferences.user_id
WHERE preferences_key = „UserComment“
AND (preferences_value = “ OR preferences_value IS NULL)
ORDER BY preferences_value ASC
Also liste ALLE Einträge der Tabelle User auf und sofern vorhanden die Einträge der Tabelle preferences_key die entweder NULL (noch nie berührt) oder „“ (bereits mal gespeichert, aber Eintrag wieder gelöscht.)
(Prüfung auf Einträge mit nur Leerezeichn haben wir hier vernachlässigt)
Das ist aber falsch, denn hier sind nur die User enthalten, die zumindest einen Eintrag auf der rechten Seite des Joins haben, also nicht das erwartet LEFT JOIN Ergebnis…
| id | login | first:name | last_name | preferences_key | preferences_value | |
|---|---|---|---|---|---|---|
| 1 | user01 | Franz | Weiß | UserComment | [BLOB – 0 B] | |
| 5 | user05 | Karl | Schuster | UserComment | [BLOB – 0 B] | |
| 6 | user06 | Gerald | Lampl | UserComment | [BLOB – 0 B] |
Da es auf der rechten Seite des JOINs für alle User IDs zumindest ein Ergebnis (mit einem anderen preference_key wie e-mail) gibt kommt es hier schon mal zu keinen NULL-Datensätzen auf der rechten Seite und das WHERE listet dann entsprechend nur noch Zeilen mit „“ – die also bereits zumindes mal einen Wer gespeicher hatten der aber wieder gelöscht wurde.
Nun die richtige Version:
SELECT id, login, first_name, last_name, preferences_key, preferences_value FROM users
LEFT JOIN (SELECT * FROM user_preferences WHERE preferences_key=’UserComment‘) as pk
ON users.id = pk.user_id
WHERE preferences_value = “ OR preferences_value IS NULL
Die rechte Seite wird damit also erst mal zu einer neuen Tabelle gemacht, in der es keine anderen Einträge als ‚UserComment‘ gibt und erst diese Tabelle wird mit dem LEFT JOIN verbunden.
Hier sieht man nun auch die Einträge, die den preferences_key gar nicht enthalten haben – so haben wir uns das vom LEFT JOIN auch erwartet…
| id | login | first:name | last_name | preferences_key | preferences_value | |
|---|---|---|---|---|---|---|
| 1 | user01 | Admin | OTRS | NULL | NULL | |
| 2 | user02 | Franz | Weiß | UserComment | [BLOB – 0 B] | |
| 3 | user03 | Robert | Puffer | NULL | NULL | |
| 4 | user04 | Thorsten | Pußwald | NULL | NULL | |
| 5 | user05 | Karl | Schuster | UserComment | [BLOB – 0 B] | |
| 6 | user06 | Gerald | Lampl | UserComment | [BLOB – 0 B] |
Der Eintrag [BLOB – 0 B] kommt übigens aus dem phpMyAdmin und bedeutet einen leeren String im Falle einer Felddefinition BLOB…
