Abfrage Problem für MultiTag-Suche

[Antz]

Servus.
Ich bin dabei eine Arte MultiTag-Suche zu realisieren. Das bedeutet, dass ich über ein Formular verschiedene Tags schreiben oder auswählen kann, mit denen ich dann eine gezielte Suche nach Beiträgen, Bildern, oder Projekten, eben den Dingen suchen kann, die mit den Tags versehen wurden.

Die sehr vereinfachte Struktur der Tabelle ist im Folgendem zu sehen:

+--------------------------+   +------------------+   +-----------------+
|projects                  |   |tags              |   |join             |
|                          |   |                  |   |                 |
|project_id|title          |   |tag_id|title      |   |project_id|tag_id|
+----------+---------------+   +------+-----------+   +---------+-------+
|1         |Alfa Project   |   |1     |Alfa Tag   |   |1        |1      |
|2         |Bravo Project  |   |2     |Bravo Tag  |   |1        |2      |
|3         |Charlie Project|   |3     |Charlie Tag|   |2        |1      |
|4         |Delta Project  |   |4     |Delta Tag  |   |3        |1      |
|5         |Echo Project   |   |5     |Echo Tag   |   |3        |2      |
|6         |Foxtrot Project|   |6     |Foxtrot Tag|   |3        |4      |
|...       |...            |   |...   |...        |   |...      |...    |
+----------+---------------+   +------+-----------+   +---------+-------+

Mit folgender Abfrage erhalte ich je Zeile den Projektnamen mit einem Tag:

SELECT projects.title AS project, tags.title AS tag
FROM projects
INNER JOIN join USING ( project_id )
INNER JOIN tags USING ( tag_id)



+-------------------------+
|Ausgabe                  |
|                         |
|project        |tag      |
+---------------+---------+
|Alfa Project   |Alfa Tag |
|Alfa Project   |Bravo Tag|
|Bravo Project  |Alfa Tag |
|Charlie Project|Alfa Tag |
|Charlie Project|Bravo Tag|
|Charlie Project|Delta Tag|
|...            |...      |
+---------------+---------+

Ich möchte nun eine relative und eine absolute Suche realisieren, das heißt:
1) Bei der Suche nach 'Alfa Tag, Bravo Tag' sollen alle Projekte angezeigt werden, die einen der gesuchten Tags enthalten.
2) Bei der Suche nach 'Alfa Tag, Bravo Tag' sollen nur die Projekte angezeigt werden, die alle gesuchten Tags enthalten.

+-------------------------+   +-------------------------+
|Ausgabe zu 1)            |   |Ausgabe zu 2)            |
|                         |   |                         |
|project        |tag      |   |project        |tag      |
+---------------+---------+   +---------------+---------+
|Alfa Project   |Alfa Tag |   |Alfa Project   |Alfa Tag |
|Alfa Project   |Bravo Tag|   |Alfa Project   |Bravo Tag|
|Bravo Project  |Alfa Tag |   |Charlie Project|Alfa Tag |
|Charlie Project|Alfa Tag |   |Charlie Project|Bravo Tag|
|Charlie Project|Bravo Tag|   +---------------+---------+
+---------------+---------+

Zu 1) Das ist nicht so schwer da ich das mit eine WHERE OR Abfrage machen kann:

SELECT projects.title AS project, tags.title
FROM projects
INNER JOIN join USING ( project_id )
INNER JOIN tags USING ( tag_id)
WHERE tags.title = 'Alfa Tag'
   OR tags.title = 'Bravo Tag'

Bei 2) habe ich vermutlich einen Denkfehler, jedoch verstehe ich nciht wieso

Achtung: Fehlerhafter Code

SELECT projects.title AS project, tags.title
FROM projects
INNER JOIN join USING ( project_id )
INNER JOIN tags USING ( tag_id)
WHERE tags.title = 'Alfa Tag'
  AND tags.title = 'Bravo Tag'

Wenn doch mein Ergebnis durch die Abfrage (ohne die Bedingung [WHERE] ) die Ergebnisse in den Zeilen liefern die ich benötige für eine Abfrage mit AND... Ach Mensch, ich glaub ich weiß warum das nciht funktioniert... Das AND bezieht sich nur auf das was pro Zeile angezeigt wird, iwie auch logisch. Aber dann weiß ich nicht wie ich das so filtern kann dass ich nur noch die Projekte angezeigt bekomme, die alle Tags enthalten, die ich in der Suche, im Formular, angegeben habe..

Kann mir wer weiterhelfen?

[Falk Prüfer]

Hallo,

...Aber dann weiß ich nicht wie ich das so filtern kann dass ich nur noch die Projekte angezeigt bekomme, die alle Tags enthalten, die ich in der Suche, im Formular, angegeben habe..

Sowas löst man z.B. mit Subselects:
[highlight=sql]
SELECT projects.title AS project, tags.title
FROM projects
INNER JOIN join USING ( project_id )
INNER JOIN tags USING ( tag_id)
WHERE exists (
select 'X' from tags itags
where itags.tag_id = tags.tag_id
and itags.title = 'Alfa Tag'
)
and exists (
select 'X' from tags itags
where itags.tag_id = tags.tag_id
and itags.title = 'Bravo Tag'
)
[/highlight]

Gruß Falk

[Antz]

Ja, das habe ich mir fast gedacht. Diesen Tip hast du mir schon mal in nem anderen Zusammenhang gegeben. Hatte mir auch schon ne Lösung gebastelt mit weiteren Sub-Selects aber ich dachte nicht, dass das richtig sein könnte. Dauern solche Anfragen dann nicht bedeutend länger um ein Ergebnis zu liefern? Was ich meine ist, ob es eine elegantere Lösung meines Problems gibt oder ein vernünftiges im allgemeinen anerkanntes multiple-tag-system?
Meines mache ich nur aus lernzwecken, dass ich mir ein eigenes ausdenke jedoch mit dem Ziel, eine finale elegante Lösung zu finden.

Danke auf jeden Fall für die Hilfe!

Gruß

[Falk Prüfer]

Alternativ ist auch eine Gruppierung mgl., wenn der TagTitle nicht im Ergebnis benötigt wird:
[highlight=sql]
SELECT projects.title AS project
FROM projects
INNER JOIN join USING ( project_id )
INNER JOIN tags USING ( tag_id)
WHERE tags.title in ('Alfa Tag','Bravo Tag')
group by projects.title
having count(*) = 2
[/highlight]
Gruss Falk

[Antz]

[...]
[highlight=sql]
WHERE exists (
select 'X' from tags itags
where itags.tag_id = tags.tag_id
and itags.title = 'Alfa Tag'
)
[/highlight]
[...]

Öhm was soll im SubSelect das SELECT 'X' ?
Was soll ich denn dort 'selecten' ?
Das was ich brtauche habe ich doch im Allumfassenden Select drin:
[highlight=sql]
SELECT projects.title AS project, tags.title
[/highlight]

[Antz]

Zu der Aussage, dass man mit deiner zweiten Lösung keine Tag-Namen anzeigen lassen kann, möchte ich noch etwas für die jenigen Anhängen, die ein Ähnliches Problem haben.

Mit dem folgen SQL-Statement erhält man ein 2-Splaten-Ergebnis. In der ersten den Projectnamen und in der 2. Spalte mit Komma getrennt alles Tags des jeweiligen Projects. Wobei natürlich nur die Projecte gelistet werden, die alle gesuchten Tags enthalten.

[highlight=sql]
SELECT projects.title AS project, (
SELECT GROUP_CONCAT(tags.title SEPARATOR ', ')
FROM tags
INNER JOIN `join` USING(tag_id)
WHERE projects.project_id = `join`.project_id
) AS tags
FROM projects
INNER JOIN `join` USING ( project_id )
INNER JOIN tags USING ( tag_id)
WHERE tags.title IN ('Alfa Tag','Bravo Tag')
GROUP BY projects.title
HAVING count(*) = 2
[/highlight]

Das Ergebnis würde dann wie folgt aussehen:

+----------------------------------------------+
|Ausgabe                                       |
|                                              |
|project        |tags                          |
+---------------+------------------------------+
|Alfa Project   |Alfa Tag, Bravo Tag           |
|Charlie Project|Alfa Tag, Bravo Tag, Delta Tag|
+---------------+------------------------------+

Zu erwähnen wäre auch noch, dass dies hier natürlich ein Pseudoprojekt ist was sehr stark vereinfacht wurde. Ich habe z.B. noch eine weitere Tabelle, in der Search-Strings nochmal mit den einzelnen Tags verknüpft sind. Damit kann man z.B. erreichen, dass, wie hier, sich ein Projekt effektiver gefunden werden lässt. Der eine sucht nach 'Alfa Tag' der andere nach 'Alfa-Tag', oder wieder ein anderer nach 'Alfatag'. In der Tabelle Search-Strings würden dann 'Alfa Tag', 'Alfa-Tag' und 'Alfatag' jeweils mit der Tabellenspalte tag_id = 1 in der Tabelle 'tags' verknüpft werden.
Eine weitere Sache ist, dass man noch eine Funktion unterbringen muss, die Groß- und Kleinschreibung außer Acht lässt, oder dass die Eingabe des Users nciht nur validiert sondern auch in Kleinbuchstaben umgewandelt wird und die Einträge in der DB auch. Und so weiter ....
Ich danke nochmals für die Hilfe und hoffe gleichzeitig, dass durch meine Probleme und die meist daran anknüpfende Lösung auch anderen hilft!

Gruß

[ebis]

Öhm was soll im SubSelect das SELECT 'X' ?

Ein 'X' ist ein 'X' ist ein 'X'

Was soll ich denn dort 'selecten' ?

Das 'X'

Das was ich brauche habe ich doch im Allumfassenden Select drin:
[highlight=sql]
SELECT projects.title AS project, tags.title
[/highlight]

erfasst

aber beide Subselects stehen in der WHERE-Klausel, so das nur Datensätze zurückgeliefert werden, wenn das jeweilige Subselect aufgrund seiner WHERE-Klausel ein 'X' liefert...

[Antz]

Ah, also kommt dan je SubSelect dann ncoh mal das was im Allumfassenden Selct drinne steht dann nochmal rein? Versteh ich das richtig?

[Falk Prüfer]

Ah, also kommt dan je SubSelect dann ncoh mal das was im Allumfassenden Selct drinne steht dann nochmal rein? Versteh ich das richtig?

Nein! Das was innerhalb eines EXIST tatsächlich selektiert wird, ist völlig irrelevant. Manche nehmen eine 1 und bei mir ist es eben ein X.Du kannst auch ein a eine 9 ein beliebiges Feld (welches nie NULL ist) oder sonstwas nehmen, es muss halt nur irgendwas EXISTieren.

Gruss Falk

[Antz]

Ah, verstehe, worunter kann ich dazu noch ein paar Infos finden? Damit ich das endlich mal richtig verstehe? Bitte sagt nciht, dass ich das SQL-Handybuch lesen soll

[Falk Prüfer]

Wie wäre es mit einer Einführung in SQL!?

Gruß Falk

[Antz]

So nun habe ich noch eine Frage; ich habe nämlich keine Ahnung wonach ich z.B. bei google suchen soll.
Zur Erinnerung:
Ich lasse mir aus der Datenbank alle die Projekte ausgeben, deren Tags mit meinen gesuchten übereinstimmen. Wenn ein Projekt mit mehreren meiner gesuchten Tags verknüpft ist, würde ich beim Weglassen der Zeile GROUP BY projects.title je Projekt, je Tag eine neue Zeile erhalten: Angenommen ich suche nach den Tags: 'Alfa Tag', 'Bravo Tag' und 'Delta Tag':
[highlight=sql]
SELECT projects.title AS project, tags.title AS tag
FROM projects
INNER JOIN `join` USING (project_id)
INNER JOIN tags USING (tag_id)
WHERE tags.title = 'Alfa Tag'
OR tags.title = 'Bravo Tag'
OR tags.title = 'Delta Tag'


+-------------------------+
|Ausgabe |
| |
|project |tag |
+---------------+---------+
|Alfa Project |Alfa Tag |
|Alfa Project |Bravo Tag|
|Bravo Project |Alfa Tag |
|Charlie Project|Alfa Tag |
|Charlie Project|Bravo Tag|
|Charlie Project|Delta Tag|
|... |... |
+---------------+---------+
[/highlight]

Mit GROUP BY projects.title bekäme ich dann:[highlight=sql]
SELECT projects.title AS project, (
SELECT GROUP_CONCAT(tags.title SEPARATOR ', ')
FROM tags
INNER JOIN `join` USING(tag_id)
WHERE projects.project_id = `join`.project_id
) AS tags
FROM projects
INNER JOIN `join` USING (project_id)
INNER JOIN tags USING (tag_id)
WHERE tags.title = 'Alfa Tag'
OR tags.title = 'Bravo Tag'
OR tags.title = 'Delta Tag'


+----------------------------------------------+
|Ausgabe |
| |
|project |tags |
+---------------+------------------------------+
|Alfa Project |Alfa Tag, Bravo Tag |
|Bravo Project |Alfa Tag |
|Charlie Project|Alfa Tag, Bravo Tag, Delta Tag|
+---------------+------------------------------+
[/highlight]

Nun mein Problem:
Um in das Tag-System eine Wertung bzw. das mein Tag-System die Ausgabe nach Priorität anzeigen kann, möchte ich die erzielten Treffer meiner gesuchten Tags zählen und nach diesen sortieren. Somit ergäbe sich dann folgende Ausgabe:
[highlight=sql]
+------------------------------------------------------+
|Ausgabe |
| |
|project |tags |treffer|
+---------------+------------------------------+-------+
|Charlie Project|Alfa Tag, Bravo Tag, Delta Tag|3 |
|Alfa Project |Alfa Tag, Bravo Tag |2 |
|Bravo Project |Alfa Tag |1 |
+---------------+------------------------------+-------+
[/highlight]

Ich denke ich kann das mit COUNT realisieren jedoch weiß ich nicht wie ich die einzelnen treffer zählen kann.

[dibo33]

Ein ,COUNT(tags.title) AS treffer nach AS tags sollte es tun.

Edit: bei Mehrfachzählung könntest du es auch mit COUNT(DISTINCT tags.title) versuchen.

[Antz]

Jo, das war's. So einfach; war auch grade daruaf gekommen. Danke dennoch.

[dibo33]

Wobei du dir die korrelierende Subquery eigentlich sparen kannst
[highlight=sql]
SELECT projects.title AS project
, GROUP_CONCAT(tags.title SEPARATOR ', ') AS tags
, COUNT(tags.tag_id) AS treffer
FROM projects
INNER JOIN `join` USING (project_id)
INNER JOIN tags USING (tag_id)
WHERE tags.title IN ('Alfa Tag', 'Bravo Tag', 'Delta Tag')
GROUP BY project
ORDER BY treffer DESC
[/highlight]

Edit: GROUP BY angepasst