SQl-Abfrage vereinfachen

[sven]

Hallo!

Ich habe mir ein kleines Redaktionssystem gebastelt, das wie folgt funktioniert: Es gibt eine Tabelle in der die Seiten gespeichert werden.

table mt_seiten
|-----------|...
| ID | NAME | ...
|-----------|...
...

Weiters gibt es eine Tabelle mit Versionen des eigentlichen Inhaltes

rt_versionen
|-------------------------------------
| ID | SEITEN_ID | INHALT | PUBLTIME
...

Die Spalte PUBLTIME sagt aus, ab wann diese Version gültig ist. Das bis wann ergibt sich von selbst. Entweder ohne Ende oder bis zur nächsten PUBLTIME einer Version zur selben Seite.
SEITEN_ID ist natürlich der Pointer auf mt_seiten

Wenn die Seite mit der ID=x ausgeliefert wird lautet der SQL-Query:

SELECT * from rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID WHERE mt_seiten.ID = x && rt_versionen.PUBLTIME < NOW() ORDER BY PUBLTIME LIMIT 1;

Mehr brauchte ich bis jetzt nicht. Ich muss das System aber jetzt ausbauen und muss jetzt mehrere Seiten auf einmal lesen, weil ich sie gesammelt auf einer anderen Seite darstellen muss.

Sagen wir ich brauche die Seiten mit den IDs 4,10,18 und 24. Mein Problem ist jetzt:
Ich kann natürlich nicht wie oben nach PUBLTIME sortieren und nur das erste Ergebnis nehmen.

Natürlich könnte ich es in einer Schleife lösen und 4 einzelne Abfragen jeweils wie oben machen.

Wäre aber nicht eine reine SQL-Lösung schöner. Außerdem geht es da in einem Fällen um eine ganze Menge Seiten. Wäre also nicht sehr effizient.

Ich komme aber die ganze Zeit auf keine Lösung. Dachte eine Weile an Subqueries mit mySQL. Aber in der Subquery müsste ja dann eine Art rückbezüglicher Teil sein? Außerdem geht es nicht. Ich hab nur mySQL 4.0 und nicht 4.1 zu Verfügung.
Habt ihr Hinweise oder Tipps, die mich auf die richtige Fährte bringen könnten?

[sven]

danke!

gruß sven

[Axel Richter]

Hallo,

Wenn die Seite mit der ID=x ausgeliefert wird lautet der SQL-Query:

SELECT * from rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID WHERE mt_seiten.ID = x && rt_versionen.PUBLTIME < NOW() ORDER BY PUBLTIME LIMIT 1;

^ Hier bekommst Du aber den kleinsten (ältesten) Wert für PUBLTIME. Ist das so gewollt?
Bitte ersetze den * durch die Spaltenbezeichner, die Du _wirklich_ benötigst.

Sagen wir ich brauche die Seiten mit den IDs 4,10,18 und 24.

SELECT mt_seiten.ID, mt_seiten.NAME, tr_versionen.INHALT, rt_versionen.PUBLTIME
FROM rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID
WHERE (mt_seiten.ID = 4 OR mt_seiten.ID = 10 OR mt_seiten.ID = 18 OR mt_seiten.ID = 24) AND rt_versionen.PUBLTIME < NOW() ORDER BY mt_seiten.ID, rt_versionen.PUBLTIME;

oder

SELECT mt_seiten.ID, mt_seiten.NAME, tr_versionen.INHALT, rt_versionen.PUBLTIME
FROM rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID
WHERE mt_seiten.ID IN (4,19,18,24) AND rt_versionen.PUBLTIME < NOW() ORDER BY mt_seiten.ID, rt_versionen.PUBLTIME;

ergibt ein Resultset mit den gewünschten mt_seiten.IDs, deren rt_versionen.PUBLTIME vor jetzt liegt. Dieses ist aufsteigend sortiert nach mt_seiten.ID und innerhalb der mt_seiten.ID nach PUBLTIME. Dadurch steht der älteste Eintrag je mt_seiten.ID immer am Anfang der jeweiligen mt_seiten.ID. Etwa so:

mt_seiten.ID   rt_versionen.PUBLTIME
4              21.12.2004
4              05.01.2005
19             10.12.2004
19             15.12.2004
19             01.01.2005
...

Mit Hilfe einer Programmiersprache kannst Du nun dieses Resultset entsprechend auswerten.

viele Grüße

Axel

[sven]

Hallo Axel!

^ Hier bekommst Du aber den kleinsten (ältesten) Wert für PUBLTIME. Ist das so gewollt?

Hab grad keinen Zugriff, aber ich dachte schon, dass da noch ein ASC drinnen war.

Bitte ersetze den * durch die Spaltenbezeichner, die Du _wirklich_ benötigst.

War nur um Tipparbeit zu sparen ;) Hätte ich erwähnen sollen.

SELECT mt_seiten.ID, mt_seiten.NAME, tr_versionen.INHALT, rt_versionen.PUBLTIME
FROM rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID
WHERE mt_seiten.ID IN (4,19,18,24) AND rt_versionen.PUBLTIME < NOW() ORDER BY mt_seiten.ID, rt_versionen.PUBLTIME;

Super, immer noch besser als x Abfragen zu machen.

Mit Hilfe einer Programmiersprache kannst Du nun dieses Resultset entsprechend auswerten.

Ich war nur so verbohrt in eine reine SQL-Lösung und hab als Alternative nur noch x Queries gesehen.

Das ist von der Performance her sicher harmlos gegen die andere Vorgangsweise.

Reine SQL-Lösung gibts dafür aber nicht, oder?

Ich stell mir eine virtuelle Spalte EXPTIME vor, die durch ein SELECT definiert ist:

SELECT MAX(PUBLTIME) from this.table WHERE SEITEN_ID = this.SEITEN_ID & PUBLTIME < NOW();

und dann mach ich nur noch:

SELECT ... FROM rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID WHERE PUBLTIME < NOW() && EXPTIME > NOW();

Das Feature gibt es natürlich nicht ;) aber vielleicht fällt jemand anders etwas dazu ein.

danke für die Lösung, die ich heut abend gleich mal umsetze
Sven

[ziegenmelker]

Hallo,

rt_versionen
|-------------------------------------
| ID | SEITEN_ID | INHALT | PUBLTIME
...

wenn du diese Tabelle z.B. um eine successor_id erweiterst, die immer auf die ID des Nachfolger-Datensatzes verweist und nur im Falle, daß es einen solchen nicht gibt, auf 0 gesetzt ist, dann hast du ein einfaches Kriterium, nur die jeweils letzten Datensätze zu selektieren.
Also eine verkettete Liste erzeugen.

cu,
ziegenmelker

[Ilja]

yo,

SELECT * from rt_versionen LEFT JOIN mt_seiten ON mt_seiten.ID = rt_versionen.SEITEN_ID WHERE mt_seiten.ID = x && rt_versionen.PUBLTIME < NOW() ORDER BY PUBLTIME LIMIT 1;

der LEFT JOIN ist hier meiner meinung nach nicht notwendig, sondern du kannst einen INNER JOIN verwenden. in der tabelle rt_versionen wird es wohl keinen datensatz geben, der nicht auf einen datensatz in der mt_seiten tabelle verweist (Fremdschlüssel).

rt_versionen.PUBLTIME < NOW()

diese bedingung kann ich auch nicht nachvollziehen, es sei den es gibt schon versionen, die in einen zeit/datum eintrag haben, der in der zukunft, bzw. in der gegenwart liegt. wenn dies nie der fall sein sollte, kann man sich den auch sparen. aber dies kann ich nicht wirklich beurteilen, eben nur ein tipp.

Sagen wir ich brauche die Seiten mit den IDs 4,10,18 und 24. Mein Problem ist jetzt:
Ich kann natürlich nicht wie oben nach PUBLTIME sortieren und nur das erste Ergebnis nehmen.

eine lösung mit allen spaltenwerten fällt mir spontan erst mal nur mit einer korelierenden unterabfrage ein. wenn ein mysql update aber gar nicht geht, dann versuch mal folgende abfrage, vielleicht hilft es ja ein wenig.

SELECT m.id, m.name, MAX(PUBLTIME) AS PUBLTIME
FROM rt_versionen AS r, mt_seiten AS m
WHERE m.ID = r.SEITEN_ID
AND m.ID IN (4, 10, 18, 24)
GROUP BY m.id, m.name