Hallo,
etwas schlechter Betreff, aber mir fällt nichts besseres ein :(
Mein Problem ist jetzt folgendes:
Jedes Bild kann in mehrere Kategorien einsortiert werden. Die
Verknüpfungdaten werden in der Tabelle kategorien_bilder gespeichert. Es
sollen jetzt nur die Bilder gezeigt werden, die in allen ausgewählten
Kategorien sind ($ids). Leider werden bisher alle Bilder zurückgegeben
die auch nur in einer der ausgewählten Kategorie sind.
Meine "Grundabfrage" die ich korrigieren will:
1
SELECT a.ID, a.Bildpfad FROM bilder AS a
2
LEFT JOIN kategorien_bilder AS b ON (a.ID = b.Bild_ID)
3
WHERE Kategorie_ID IN ($ids)
4
#$ids ist hier: 1, 2
Warum die Abfrage so nicht funktioniert (zuviel liefert), ist mir klar.
Es soll aber nur Bild 1 liefern und nicht beide, da ja nur Bild 1 in
beiden Kategorien ist.
Vermutlich fehlt mir nur irgendein Schlüsselwort um das zu lösen, aber
ich weiß einfach nicht was das sein könnte.
Eine schlechte Lösung wäre notfalls die Daten für alle angeforderten
Kategorie_ID abzufragen und dann in PHP zu prüfen.
Tabellen:
bilder
+---+---------+
|ID | Bildpfad|
+---+---------+
| 1 | abc.jpg |
+---+---------+
| 2 | xyz.jpg |
+---+---------+
kategorien
+---+---------+
|ID | Katname |
+---+---------+
| 1 | testkat |
+---+---------+
| 2 | abc |
+---+---------+
kategorien_bilder
+-------+------------+
|Bild_ID|Kategorie_ID|
+-------+------------+
| 1 | 1 |
+-------+------------+
| 1 | 2 |
+-------+------------+
| 2 | 1 |
+-------+------------+
Das Problem ist hier zu formulieren "Alle" Kategorien...
Wenn gilt, das jedes Bild jeder Kategorie nur einmal zugeordnet sein
kann (UNIQUE auf Bild_ID,Kategorie_ID) könntest du bestenfalls sowas
machen wie:
1
SELECT * FROM bilder b where b.ID in
2
(SELECT id FROM kategorien_bilder GROUP BY Bild_ID HAVING count(Kategorie_ID) = (SELECT count(*) FROM kategorien))
Og subquerys an der Stelle erlaubt sind müsste man mal probieren. Falls
sich die Anzahl Kategorien nicht häufig ändert könnte man auch halt
Schrittweise jene Bilder bestimmen.
Ich sehe gerade das du ja die IDs scheinbar vorgegeben hast, also nicht
wirklich ALLE sondern alle aus einer Menge...
1
SELECT * FROM bilder b where b.ID in
2
(SELECT Bild_ID FROM kategorien_bilder WHERE Kategorie_ID in ($ids) GROUP BY Bild_ID)
Mit deinem Join könnte man das ggf. also etwa so machen
1
SELECT a.ID, a.Bildpfad FROM bilder AS a
2
LEFT JOIN kategorien_bilder AS b ON (a.ID = b.Bild_ID AND b.Bild_ID in (SELECT Bild_ID FROM kategorien_bilder WHERE Kategorie_ID in ($ids) GROUP BY Bild_ID))
Soll jetzt nur als Idee dienen, habe es nicht live getestet...
Läubi .. schrieb:> Wenn gilt, das jedes Bild jeder Kategorie nur einmal zugeordnet sein> kann
Ja das gilt. Jedes Bild kann beliebig vielen Kategorien zugeordnert
werden, aber jeder Kategorie nur 1x.
Läubi .. schrieb:> Og subquerys an der Stelle erlaubt sind
Die sind kein Problem.
1
SELECT * FROM bilder b where b.ID in
2
(SELECT Bild_ID FROM kategorien_bilder WHERE Kategorie_ID in ($ids) GROUP BY Bild_ID)
Der bringt das gleiche wie meine Abfrage, nur dass halt doppelte per
GROUP entfernt werden. GROUP BY hatte ich auch schon mal überlegt, aber
das nimmt ja nur doppelte aus der Ergebnismenge. Ich bräuchte also das
Gegenteil davon: entferne alle die nicht doppelt sind.
Die Anzahl der Kategorien erhöht sich unregelmäßig. Wenn ich eine
gewisse Zahl an Bilder zu einem Thema habe, wird normal eine neue
Kategorie erstellt.
Z.B. jetzt mehr als 10 Bilder wo auch Bäume drauf sind, dann gibts eine
Kategorie Bäume. Die Bilder können natürlich auch noch in eine Kategorie
Häuser, Landschaft oder sonst was.
Ich glaube es wird auf PHP-Lösung rauslaufen :( Aber vielleicht schaffe
ich es noch irgendwie mit Subqueries.
Läubi .. schrieb:> SELECT * FROM bilder b where b.ID in> (SELECT id FROM kategorien_bilder GROUP BY Bild_ID HAVING> count(Kategorie_ID) = (SELECT count(*) FROM kategorien))
Das geht; habe es gerade mal ausprobiert in MySQL:
1
SELECT * FROM bilder b where b.ID in
2
(SELECT b_id FROM bilder_kategorien GROUP BY B_ID HAVING count(K_ID) = (SELECT count(*) FROM kategorien))
D. I. schrieb:> Das geht; habe es gerade mal ausprobiert in MySQL
Ist aber das Problem, dass man dann auch ALLE und nicht alle aus einer
Auswahl kriegt, ich vermute mal es gib 1..n Kategoerien wo der User 1..m
(mit m<=n) auswählt und der TE will die zugehörigen Bilder. Nicht so
schön aber aber selten:
1
SELECT * FROM (
2
SELECT k.Bild_id, k.Kategorie_ID FROM kategorien b, kategorien_bilder k WHERE b.id=k.Bild_ID and k.Kategorie_ID = 1
3
UNION ALL
4
SELECT k.Bild_id, k.Kategorie_ID FROM kategorien b, kategorien_bilder k WHERE b.id=k.Bild_ID and k.Kategorie_ID = 2
5
) AS X GROUP BY Bild_id HAVING count(Kategorie_ID) = 2;
Dann klappt es auch mit mehreren Kategorien.
Muss dann aber halt immer generiert werden -> nicht so schön... Ich bin
auch kein Freund davon zuviel Programmlogik in Kilometerlange Querys zu
packen...
Was auch noch ginge, aber ggf. schlechte Performance:
1
SELECT b.id, b.Bildpfad FROM bilder b, kategorien k, kategorien_bilder kb WHERE b.ID = kb.Bild_ID AND k.ID = Kategorie_ID GROUP BY kb.Bild_id HAVING GROUP_CONCAT(kb.Kategorie_ID ORDER BY kb.Kategorie_ID) = '1,2';
Läubi .. schrieb:> ich vermute mal es gib 1..n Kategoerien wo der User 1..m> (mit m<=n) auswählt und der TE will die zugehörigen Bilder. Nicht so> schön aber aber selten:
So ist es, aber wieso nicht schön?
Solange die Ergebnisse auf eine Kategorie beschränkt ist, lässt sich
damit wunderbar arbeiten. Nur wird es schwer eine ganze Kategorie zu
durchsuchen um bestimmte Bilder zu erhalten:
Suche alle Bilder von Ort XYZ die Gewässer zeigen, zwischen hunderten
anderen Bildern von Ort XYZ die Gebäude zeigen. Das wäre mit der Abfrage
auf mehrere Kategorien einfach.
Troll schrieb:> So ist es, aber wieso nicht schön?
Das bezog sich auf meine Lösung mit den UNIONS, da man dann eine UION
pro abzufragende Kategorie benötigt sich also den Query erst mal als
String zusammenbauen muss.
Lösung 2 hat den Nachteil, dass das Filtern auf Stringebene passiert,
d.h. die DB bestimmt erst mal alle Bilder incl. Kategorien, gruppiert
diese und wirft dann nicht benötigte raus, das kann eventuell langsam
sein wenn es entsprechend viele Kategorien und Bilder gibt, kommt aber
deiner Wunschlösung am nächsten. Man könnte das noch einschränken indem
man die potentiellen Kandidaten in der Where clausel mittels IN
einschränkt.
SELECT k.Bild_id, k.Kategorie_ID FROM kategorien b, kategorien_bilder k WHERE b.id=k.Bild_ID and k.Kategorie_ID = 1
3
UNION ALL
4
SELECT k.Bild_id, k.Kategorie_ID FROM kategorien b, kategorien_bilder k WHERE b.id=k.Bild_ID and k.Kategorie_ID = 2
5
) AS X GROUP BY Bild_id HAVING count(Kategorie_ID) = 2;
Wenn ich das richtig sehe, muss ich für die Abfrage mehrerer Kategorien
immer mehr Subqueries zusammenbauen. Das dürfte auf Dauer nicht mehr
sehr schnell sein.
Läubi .. schrieb:
1
> SELECT b.id, b.Bildpfad FROM bilder b, kategorien k, kategorien_bilder kb WHERE b.ID = kb.Bild_ID AND k.ID = Kategorie_ID GROUP BY kb.Bild_id HAVING GROUP_CONCAT(kb.Kategorie_ID ORDER BY kb.Kategorie_ID) = '1,2';
Die Abfrage funktioniert zwar, dauerte bei gerade mal 2.500 Datensätzen
aber schon 0.0102 Sekunden. Das wird mit der Zeit vermutlich noch
schlechter.
Ich versuchs jetzt erstmal mit einer PHP-Lösung.
1
SELECT * FROM kategorien_daten WHERE Kategorie_ID IN (1, 2)
Dann in PHP prüfen, zusammenfügen und am Ende die Bilddaten abfragen.
Trotzdem danke für eure Hilfe. Hat mir auch etwas beim allgemeinen
SQL-Verständnis geholfen.
Troll schrieb:> Das dürfte auf Dauer nicht mehr> sehr schnell sein.
Was ist "sehr schnell"... Wenn du einen passenden Index hast kann das
auf der DB schnell gehen.
Troll schrieb:> aber schon 0.0102 Sekunden
Die Netzwerklatenz + auswertung und darstellung in PHP hat sicher einen
größeren Anteil... aber ja das wird mit größeren Datenmengen nicht
schneller. Ob das in PHP fixer geht sei mal dahingestellt, du mußt
(ebenso wie die DB) hier auch jeden Datensatz prüfen.
Allgemein muß man sowieso die ganze Streke betrachten, es bringt nix
wenn die DB fix ist du dafür aber in PHP ewig rödelst um hunderte
Datensätze abzurufen welche du gleich wieder wegwirfst.
Thread beobachten
Seitenaufteilung abschalten