Unterabfragen als Filter

[Agor]

Hallo,

ich habe ein Problem mit einer SQL Abfrage, die ich versucht habe bisher so zu lösen.

$result = mysql_query("
  SELECT
    picture_id
  FROM
    picture_tag
  WHERE
    tag_id IN (SELECT id FROM tags WHERE name = 'abc')
");

So sehen die Tabellen aus:
picture_id: picture_id, tag_id
tags: id, name

Ich möchte, dass ich die Zeile in der WHERE-Clause beliebig um mehrere tags erweitert kann und ich dann nur "picture_id"'s auswähle, die jeden Tag enthalten.

gruss
Agor

[hotti]

hi,

Ich möchte, dass ich die Zeile in der WHERE-Clause beliebig um mehrere tags erweitert kann und ich dann nur "picture_id"'s auswähle, die jeden Tag enthalten.

Zuständigkeiten klären, Ruhe bewahren, handeln.

Hotti

[Argor]

Zuständigkeiten klären, Ruhe bewahren, handeln.

Hotti

Tut mir leid, verstehe nicht ganz worauf du hinaus willst...

[hotti]

Tut mir leid, verstehe nicht ganz worauf du hinaus willst...

http://forum.de.selfhtml.org/hilfe/charta.htm#tipps-fuer-fragende

Hotti

[ChrisB]

Hi,

Tut mir leid, verstehe nicht ganz worauf du hinaus willst...

Deine Problembeschreibung lässt aber auch noch genug Fragen offen.

Wenn du deine Frage nicht im PHP-Bereich gestellt hättest, wo sie wenig verloren hat, sondern im Datenbankbereich - dann wärest du schon beim Erstellen des Threads darauf hingewiesen worden, dass du bitte so wichtige Informationen wie den Namen des Datenbanksystems und dessen Versionsnummer nicht verschweigen sollst.

Und darüber hinaus sind genauere Erklärungen zur Datenstruktur (im Gegensatz zu einem kurzen „das sind die zwei Tabellen: [zwei mal Erwähnung von nicht mehr als Spaltennamen]”) gern gesehen;
als auch Angabe von Beispieldaten („wenn diese Daten sich in den genannten Tabellebn befinden, dann möchte ich mit meiner Query genau jene Ausgabe bekommen: …”)

MfG ChrisB

[Agor]

Wenn du deine Frage nicht im PHP-Bereich gestellt hättest, wo sie wenig verloren hat, sondern im Datenbankbereich - dann wärest du schon beim Erstellen des Threads darauf hingewiesen worden, dass du bitte so wichtige Informationen wie den Namen des Datenbanksystems und dessen Versionsnummer nicht verschweigen sollst.

Tut mir leid, da war ich mir nicht sicher wo es reingehört.
Also wie man erahnen kann benutze ich mySQL und Version 5.1.37

Und darüber hinaus sind genauere Erklärungen zur Datenstruktur (im Gegensatz zu einem kurzen „das sind die zwei Tabellen: [zwei mal Erwähnung von nicht mehr als Spaltennamen]”) gern gesehen;

Ich dachte, dass die Tabellen beschreibung aussreichte.
Also ich habe so eine Art relationship zwischen den tabellen pictures und tags.
picture_tag verbinden diese indem sie einmal picture_id und tag_id enthält.
tags enthält den Namen, die ID und andere unwichtige Informationen. pictures enthält die Infos zum Bild und eine ID.

Ich möchte gern die Möglichkeit haben, die Bilder zu filtern und zwar indem ich die tags(per PHP) angebe. Nun will ich, dass schon bei der mySQL Abfrage nur Ergebnisse erscheinen, die jeden angegeben Tag besitzen.

$result = mysql_query("
  SELECT
    picture_id
  FROM
    picture_tag
  WHERE
    tag_id IN (SELECT id FROM tags WHERE name = 'abc')
");

benutze ich um nur Bilder zu laden, die auch den Tag abc enthalten. Jetzt möchte ich aber beliebig viele Tags dazu addieren.
Der Zusatz
AND tag_id IN (SELECT id FROM tags WHERE name = 'def')
funktioniert leider nicht.

Hoffe es ist jetzt verständlich genung und jemand hat eine Lösung. Sollte meiner Meinung nach recht simple sein nur leider komm ich nicht drauf.

mfg
Argor

[ChrisB]

Hi,

Ich möchte gern die Möglichkeit haben, die Bilder zu filtern und zwar indem ich die tags(per PHP) angebe. Nun will ich, dass schon bei der mySQL Abfrage nur Ergebnisse erscheinen, die jeden angegeben Tag besitzen.

$result = mysql_query("
  SELECT
    picture_id
  FROM
    picture_tag
  WHERE
    tag_id IN (SELECT id FROM tags WHERE name = 'abc')
");

benutze ich um nur Bilder zu laden, die auch den Tag abc enthalten. Jetzt möchte ich aber beliebig viele Tags dazu addieren.
Der Zusatz
AND tag_id IN (SELECT id FROM tags WHERE name = 'def')
funktioniert leider nicht.

Natürlich nicht - denn das müsste ja letztendlich einen Datensatz finden, in dem tag_id sowohl gleich der tag_id von 'abc' als auch der von 'def' ist.

Du willst daraus, dass sich *mehrere* Datensätze (zu einer picture_id) in der durch Bedingungen entsprechend eingeschränkten (Unter-)Ergebnismenge befinden, einen Schluss ziehen.

Zählen geht mit COUNT, und HAVING erlaubt die zurückgelieferten Datensätze nach Auswertung der WHERE-Klausel, aber vor der Lieferung des eigentlichen Ergebnisses noch mal einzuschränken. Einschränken willst du hier auf die, bei denen die Zählung (mindestens) die Anzahl der in der WHERE-Klausel angegebenenen IDs ergeben hat.

MfG ChrisB

[Agor]

Zählen geht mit COUNT, und HAVING erlaubt die zurückgelieferten Datensätze nach Auswertung der WHERE-Klausel, aber vor der Lieferung des eigentlichen Ergebnisses noch mal einzuschränken. Einschränken willst du hier auf die, bei denen die Zählung (mindestens) die Anzahl der in der WHERE-Klausel angegebenenen IDs ergeben hat.

Hallo, ich wüsste nicht wie sowas mit Count möglich ist, ich will nicht die Anzahl der Ergebnisse haben, sondern die picture_id aus der tabelle picture_id. Und das ganze sollte ihn einer array auslesbar sein. Wenn ich, wie du vorgeschlagen, Count benutze würde ich nur die Anzahl der gefundenen haben aber nicht welche es direkt waren und außerdem würde das Filtern nicht gehen. Mit der HAVING-Klausel könnte es funktionieren nur versteh ich nicht ganz, wie ich dort eine Abfrage mache, die danach wiederrum eine zweite HAVING abfrage auf deren Ergebnisse macht(Tag1 filter und alle gefundenen picture_id s nach Tag2 filtern etc).

Wäre nett wenn mir jemand dabei auf die sprünge hilft

mfg
Agor

[dedlfix]

Hi!

Wäre nett wenn mir jemand dabei auf die sprünge hilft

Die Tabelle tags ist nebensächlich. Aus der musst du nur die IDs der gewünschten Tags suchen. Die Beziehungstabelle enthält nun für jedes Tag, das einem bestimmten Bild zugeordnet ist einen Datensatz. Mit AND kannst du nicht fragen, weil sich die Bedingung auf einen Datensatz bezieht, die Information aber über mehrere Datensätze verteilt ist. Also musst du OR fragen und bekommst dann mehrere Ergebnisdatensätze pro Bild. Wenn nur mindestens ein Tag zutreffen muss ist das unproblematisch. Man könnte bildlich gesprochen sagen, dass WHERE waagerecht arbeitet, deine Ergebnismenge aber senktrecht vorliegt. Wenn alle Tags zutreffen sollen, so musst du die Information aus der Senkrechten in die Waagerechte bringen. Beispielsweise indem du nachzählst, wieviele Tags pro Bild-ID im Ergebnis enthalten sind. Da kommt das COUNT() ins Spiel. Und da du das pro Bild wissen willst, musst du über die Bild-ID gruppieren und anschließend mit HAVING nur die Gruppen (sprich Bild-IDs) nehmen, deren Anzahl stimmt.

Lo!

[Agor]

Ich habs hingekriegt! Mein Fehler war, dass ich bei der falschen Tabelle angefangen habe, ich kann die ids aus der tabelle pictures suchen und sie nach den Kriterien filtern.

$result = mysql_query("
        SELECT
          id
        FROM
          pictures
        WHERE
          id IN (
              SELECT
                picture_id
              FROM
                picture_tag
              WHERE
                tag_id IN (
                    SELECT
                      id
                    FROM
                      tags
                    WHERE
                      name = 'abc')
                ) AND
          id IN (
              SELECT
                picture_id
              FROM
                picture_tag
              WHERE
                tag_id IN (
                    SELECT
                      id
                    FROM
                      tags
                    WHERE
                      name = 'def')
                )
      ") or die(mysql_error());

Jetzt kann ich den "id IN"-Teil ganz einfach erweitern und ich kriege nur die gefilterten Ergebnisse!

[dedlfix]

Hi!

Ich habs hingekriegt!

Effizienz und Eleganz sieht anders aus. Für jeden neuen Suchbegriff fügst du zwei ineinander geschachtelte Subselects ein. Das ergibt schon bei deinem Beispiel (laut EXPLAIN) 5 abzuarbeitenden Schritte.

SELECT *
FROM pictures
WHERE id IN
 (
  SELECT picture_id
  FROM picture_tag
  WHERE tag_id IN
   (
    SELECT id
    FROM tags
    WHERE name IN ('abc', 'def')
   )
  GROUP BY picture_id
  HAVING COUNT(*) >= 2
 )

Das sind schon mal nur noch konstant drei Schritte. Oder sogar nur zwei, denn wenn du nur die ID vom Bild brauchst, kannst du die äußere Abfrage weglassen. Die 2 bei HAVING muss jeweils der Anzahl der Tags angepasst werden. Und wenn du es richtig machst, wird auch der PHP-Teil kleiner, der in die Abfrage die Tags und ihre Anzahl einfügt.

Lo!

[Erik]

So solltest du es auf keinen Fall machen.

Schau dir einfach mal "inner join" an, das sollte dein Problem lösen.

[dedlfix]

Hi!

Ich möchte gern die Möglichkeit haben, die Bilder zu filtern und zwar indem ich die tags(per PHP) angebe. Nun will ich, dass schon bei der mySQL Abfrage nur Ergebnisse erscheinen, die jeden angegeben Tag besitzen.

Die gleiche Problematik gabs vor ein paar Tagen schon mal. Schau mal dort.

Lo!