SQL abfrage über mehrere Tabellen

[Jeena Paradies]

Hallo,

Ich sitze jetzt hier gerade über einer für mich ungalublich schweeren SQL Abfrage und kriege es nicht hin sie vernünftig zu formulieren. Irgendwie habe ich absolut kein Konzept wie ich da herangehen kann/soll. Ich muss irgendwie so viel gleichzeitig berücksichtigen dass mir alles aus den Händen gleitet und ich jetzt schon seit ein paar Stunden gar nichts zusammenbekomme. Ich versuche es mal etwas zu beschreiben, vielleicht könnt ihr mir dann ein wenig helfen.

Ich habe drei MySQL Tabellen (verkürtzt):

news
  id,
  topic,
  title,
  keywords,
  teaser,
  content

content
  id,
  topic,
  title,
  keywords,
  teaser,
  content

comments
  id,
  name,
  content,
  parent

news und content sind voneinander völlig unabhängig. In comments->parent ist die id der news auf die sich dieser Kommentar bezieht. Darin befindet sich (vereinfacht dargestellt) der ganze Inhalt der ganzen Seite. Als Ausgabe möchte ich dann im Endeffekt so etwas haben (je nachdem wo der Begriff gefunden wurde:

news:     /news/id             -> /news/68
content:  /id                  -> /about
comments: /news/news_id#c_id   -> /news/68#c12

Als ich letztes Jahr schon mal gefragt hatte bekam ich schon mal eine gute Antwort:

------------------
SELECT
[combinedTables].[felda]
,[combinedTables].[feldb]  -- -> text feld zum durchsuchen
,[combinedTables].[feldc]  -- -> tabellen identifier
FROM
(SELECT ID as [felda], text as [feldb], 'tab1' as [feldc] FROM tab1
  UNION
  SELECT ID as [felda], text as [feldb], 'tab2' as [feldc] FROM tab2
  UNION
  SELECT ID as [felda], text as [feldb], 'tab1' as [feldc] FROM tab3)
as [combinedTables]
WHERE MATCH [combinedTables].[feldb] AGAINST ('wort1 wort2')
------------------

Die ich aber absolut nicht in die Wirklichkeit umsetzen kann. Ich weiß vor allem nicht wie ich dann auseinander halten kann in welcher Tabelle das Wort gefunden wurde. Wie kann ich das angehen?

Grüße
Jeena Paradies

[Ludger]

Hi,

Ich sitze jetzt hier gerade über einer für mich ungalublich schweeren SQL Abfrage und kriege es nicht hin sie vernünftig zu formulieren.

Schwere haengt mit der Gravitationskraft zusammen. Versuche diese zu vermindern.

news
  id,
  topic,
  title,
  keywords,
  teaser,
  content

content
  id,
  topic,
  title,
  keywords,
  teaser,
  content

comments
  id,
  name,
  content,
  parent

news und content sind voneinander völlig unabhängig. In comments->parent ist die id der news auf die sich dieser Kommentar bezieht.

Vielleicht mal comments.parent in comments.news_id umbenennen? (parent hoert sich gar nicht gut an)

Darin befindet sich (vereinfacht dargestellt) der ganze Inhalt der ganzen Seite. Als Ausgabe möchte ich dann im Endeffekt so etwas haben (je nachdem wo der Begriff gefunden wurde:

news:     /news/id             -> /news/68
content:  /id                  -> /about
comments: /news/news_id#c_id   -> /news/68#c12

Also, die "1:n"-Beziehung zwischen comments und news habe ich verstanden, aber welche Beziehungen bestehen noch? (Ich gehe mal davon aus, dass news und content _nicht_ voellig unabhaengig voneinander sind.) Was hat es mit dem dreimal vorkommenden Datenfeld content auf sich? (zweimal als Fremdschluessel, einmal als Selbstbezug?)

Gruss,
Ludger

[Jeena Paradies]

Hallo,

Vielleicht mal comments.parent in comments.news_id umbenennen? (parent hoert sich gar nicht gut an)

Jo eigentlich heißt das dort reference nur ist mir der Name vorhin nicht eingefallen.

Also, die "1:n"-Beziehung zwischen comments und news habe ich verstanden, aber welche Beziehungen bestehen noch? (Ich gehe mal davon aus, dass news und content _nicht_ voellig unabhaengig voneinander sind.) Was hat es mit dem dreimal vorkommenden Datenfeld content auf sich? (zweimal als Fremdschluessel, einmal als Selbstbezug?)

In content ist der jeweilige body der html Seite drinn, bzw. bei comments der einzelne Kommentar an sich. Genau das soll ja nach dem Suchbegriff durchsucht werden, genau so wie teaser, keywords und title, ne schmarn topic gibt es gar nicht es heißt eigentlich title (oh je ich weiß verwirrend) Es ist eigentlich so dass das die einzelnen Bestandteile einer HTML Seite sind:

content ist der body
keywords sind die meta keywords
teaser ist die meta description
title ist h1

es gibt zwei bereiche auf der ganzen Webpräsentation und das sind die News, die auch auf den gleichen Seiten die Kommentare beherbergen und alle anderen (so zu sagen) statischen Seiten, davon gibt es etwa 10 (die sich alle in Tabelle content befinden).

Jetzt will ich durch das einmalige aufrufen der Suche erreichen, dass mir die relevantesten Suchergebnisse als erstes erscheinen, das macht ja die MySQL Suche an sich schon einigermaßen erfolgreich mit nur einer Tabelle auf meiner Seite. Jetzt will ich aber die inhalte aus den drei Tabellen zusammenschmeißen und die Ergebnisse nach relevanz sortieren und nicht jede Tabelle einzeln. Das geht ja anscheinend seit MySQL 4.x mit UNION nur verstehe ich noch nicht wie.

Grüße
Jeena Paradies

[Ludger]

Hi,

ich habe jetzt kein Bock mich in die Sache tiefer hineinzudenken, aber:
1.) das Datendesign scheint mir suboptimal zu sein
2.) die angestrebte Volltextsuche sollte anders (ueber beizeiten angelegte Indizes) realisiert werden (Lass Dich mal von Dannie beraten ;-)
3.) der UNION-Operator ist an und fuer sich durchaus verstaendlich

Gruss,
Ludger

[Jeena Paradies]

Hallo,

Danke für deine schnelle Hilfe.

Grüße
Jeena Paradies

[Daniela Koller]

Hi Ludger

2.) die angestrebte Volltextsuche sollte anders (ueber beizeiten angelegte Indizes) realisiert werden (Lass Dich mal von Dannie beraten ;-)

Die MySQL-Volltextsuche benutzt durchaus einen speziellen Index.

Gruss Daniela

[Andreas Lindig]

Hallo Jeena,

Dein Problem scheint ja die allgemeine Relevanz-Sortierung zu sein. Ich würde das so machen, wie schon beschrieben wurde. Von den kranken Relevanz-Regeln in MySQL mal abgesehen (ich würde den Quatsch eh nicht [mehr] benutzen) - wie soll das allgemein gehen? Ist ein Begriff im Artikel mit gleicher Wertung nun relevanter, als einer im Kommentar oder nicht? Wie soll MySQL das wissen? Da müßtest Du ja einen Join haben und alle Spalten gemeinsam abfragen - mit der Folge, daß nur Begriffe gefunden werden, die in allen Spalten vorkommen. Als Verallgemeinerung könnte ich mir allenfalls vorstellen, daß Du vor der Sortierung im Script einen Durchschnitt berechnest, also "wort" hat in "comments" der Rang 0.625... und in "content" den Rang 0.015478. Das ergibt dann  eben einen Gesamtrang von... Moment-Taschenrechner-... 0.320239. Jetzt könntest Du noch, statt die Summe durch zwei zu teilen für die unterschiedlichen Tabellen einen eigenen TabellenRang vergeben und das entsprechend verrechnen. Ein GesamtRang nach MySQL-Regeln wäre ohnehin Quatsch, da wichtige Begriffe aus dem Artikel mit hoher Wahrscheinlichkeit im Kommentar wiederholt werden und dadurch an "Relevanz" verlieren würden.

Ansonsten kannst Du alles in eine Abfrage packen ungefähr (ungetestet) so:

SELECT content.content AS cont_cont, content.title AS cont_title, comments.content AS com_cont, comments.name AS com_name
FROM content, comments
WHERE
   MATCH(cont_cont)  AGAINST('wort')
OR MATCH(cont_title) AGAINST('wort')
OR MATCH(com_cont)   AGAINST('wort')
OR MATCH(com_name)   AGAINST('wort')

So, das reicht. Für genaueres müßte ich mal in meiner alten Forumssuche nachschauen, da hab ich ja auch noch diesen Fulltext-Quatsch benutzt.

Gruß, Andreas