Vernünftiger Aufbau/Abfragen für einen Belegungsplaner

[Peter Strattmann]

Hallo,

ich möchte eine Art Belegungssystem in PHP in Verbindung mit einer MySQL-Datenbank realisieren.
Hierzu wollte ich dementsprechend Zeiträume (von-bis) als Timestamp in der DB ablegen.
Da Belegungen üblicherweise tagesgenau stattfinden, wollte ich dementsprechend z.B. eine Belegung um
10 Uhr enden, die Anschlussbelegung dann um 11 Uhr beginnen lassen.

Jetzt beginnen aber meine Probleme:
Eine Abfrage, ob bereits für einen neuen Belegungszeitraum eine Belegung vorliegt, kann ich ja doch mit
BETWEEN vornehmen, aber diese query würde mir ja nur dann einen Wert zurückliefern, wenn der
geplante Belegungszeitrahmen gleich oder kleiner als eine schon gespeicherte Belegung wäre.

Was ist aber bei folgenden Beispiel-Scenarien:
Gespeicherte Belegung:
01.10.2010 11.00 Uhr bis 05.10.2010 10.00 Uhr
Gewünschte Neubelegung:
20.09.2010 11.00 Uhr bis 03.10.2010 10.00 Uhr
oder
03.10.2010 11.00 Uhr bis 04.10.2010 10.00 Uhr

Ich kann ja eigentlich schlecht hingehen und einen neuen Belegungszeitraum in einzelne Tage zerlegen und dann jeweils abfragen, ob dieser Timestamp schon belegt ist (wären ja bei 2 Belegungstagen "nur" zwei BETWEENs aber was mache ich bei einem Belegungszeitraum von 6 Wochen [6 KW * 7 Tage = 42 Timestamp-Abfragen]) ?

Umgekeht bahnt sich ja auch schon das Problem an, das ich bei einer Ausgabe einer Wochen oder Monatsübersicht auch die Zeiträume "zwischen" meinen Start- und Endtimestamps berücksichtigen müsste...

Ich hoffe das mir einer einen "Schubs" in die richtige Denkrichtung geben kann und bitte daher um eure Mithilfe.
Wichtig ist mir allerdings, das ich nur ungerne auf Klassen Dritter ausweichen möchte.

Viele Grüße
Peter

[Tom]

Hello "Peter",

das wurde ausführlich in http://forum.de.selfhtml.org/archiv/2009/6/t187994/#m1250759 diskutiert, und mMn wurde es auch bis zur Lösung gebracht...

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Hierzu wollte ich dementsprechend Zeiträume (von-bis) als Timestamp in der DB ablegen.

Warum willst du dafür Timestamps missbrauchen und kein Datetime-Feld verwenden?

Eine Abfrage, ob bereits für einen neuen Belegungszeitraum eine Belegung vorliegt, kann ich ja doch mit BETWEEN vornehmen, aber diese query würde mir ja nur dann einen Wert zurückliefern, wenn der geplante Belegungszeitrahmen gleich oder kleiner als eine schon gespeicherte Belegung wäre.

BETWEEN verarbeitet drei Werte. Du hast aber vier Werte, nämlich jeweils Start und Ende des neuen und des alten Zeitraums. Du brauchst zwei Bedingungen: die erste prüft, dass der neue Starttermin nicht in eine Belegung fällt und die zweite prüft, ob das auf den neuen Endtermin ebenso zutrifft.

Was ist aber bei folgenden Beispiel-Scenarien:
Gespeicherte Belegung:
01.10.2010 11.00 Uhr bis 05.10.2010 10.00 Uhr
Gewünschte Neubelegung:
20.09.2010 11.00 Uhr bis 03.10.2010 10.00 Uhr

Endtermin erfüllt die Bedingung nicht.

03.10.2010 11.00 Uhr bis 04.10.2010 10.00 Uhr

Start- und Endtermine erfüllen die Bedingung nicht.

Umgekeht bahnt sich ja auch schon das Problem an, das ich bei einer Ausgabe einer Wochen oder Monatsübersicht auch die Zeiträume "zwischen" meinen Start- und Endtimestamps berücksichtigen müsste...

Inwiefern? Du kannst keine Daten abfragen, die nicht vorhanden sind. Du müsstest einen Kalender anderswoher nehmen und die belegten Zeiten eintragen. Der Rest bleibt nicht markiert, ist also frei.

Lo!

[dedlfix]

Hi!

BETWEEN verarbeitet drei Werte. Du hast aber vier Werte, nämlich jeweils Start und Ende des neuen und des alten Zeitraums. Du brauchst zwei Bedingungen: die erste prüft, dass der neue Starttermin nicht in eine Belegung fällt und die zweite prüft, ob das auf den neuen Endtermin ebenso zutrifft.

Achja, eine dritte brauchst du auch noch: Von den vorhandenen Terminen darf die Anfangs- oder Endzeit nicht im neuen Zeitraum liegen. Diese Prüfung deckt den Fall ab, wenn der neue Termin quasi links und rechts über einen vorhandenen Termin hinausragt. Hier musst du nur eine der beiden Zeiten prüfen, denn wenn der vorhandene Zeitraum nicht vollständig im neuen Zeitraum liegt, ist schon eine der beiden obigen Bedingungen erfüllt.

Lo!

[Tom]

Hello,

BETWEEN verarbeitet drei Werte. Du hast aber vier Werte, nämlich jeweils Start und Ende des neuen und des alten Zeitraums. Du brauchst zwei Bedingungen: die erste prüft, dass der neue Starttermin nicht in eine Belegung fällt und die zweite prüft, ob das auf den neuen Endtermin ebenso zutrifft.

...und vorher prüfen, ob der gewünschte Endtermin größer als der gewünschte Starttermin ist.

Umgekeht bahnt sich ja auch schon das Problem an, das ich bei einer Ausgabe einer Wochen oder Monatsübersicht auch die Zeiträume "zwischen" meinen Start- und Endtimestamps berücksichtigen müsste...

Inwiefern? Du kannst keine Daten abfragen, die nicht vorhanden sind. Du müsstest einen Kalender anderswoher nehmen und die belegten Zeiten eintragen. Der Rest bleibt nicht markiert, ist also frei.

Es bleibt trotzdem die Frage, wie man einen bedingten Insert aufbaut, damit der nur durchgeführt wird, wenn ein Select() _keinen_ Datensatz geliefert hat.

Bekommt man das mit http://dev.mysql.com/doc/refman/5.1/de/insert-select.html hin?

Denn die Trennung von Select und Insert bereitet ja schon wieder andere Probleme ;-P

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Es bleibt trotzdem die Frage, wie man einen bedingten Insert aufbaut, damit der nur durchgeführt wird, wenn ein Select() _keinen_ Datensatz geliefert hat.

Nun, du kannst eine COUNT()-Subquery stellen, die schonmal die Information liefert, ob was überlappt oder nicht. Die beïnhaltet alle drei(/vier) erwähnten Bedingungen.

Bekommt man das mit http://dev.mysql.com/doc/refman/5.1/de/insert-select.html hin?

INSERT INTO table SELECT * FROM (SELECT anfang, ende, sonstige Daten) dummyAlias WHERE (count-Subquery) = 0

Ist etwas komplex, sollte aber gehen. Eine Alternative wären Transaktionen oder Locking. Wobei ich gerade feststelle, dass ich nicht weiß, ob Transaktionen atomar ablaufen. Ansonsten bleibt ja das Problem bestehen. Man könnte dann lediglich vor Abschluss der Transaktion noch einmal die Bedingung testen und gegebenenfalls ein Rollback anstoßen.

Lo!

[Tom]

Hello,

Es bleibt trotzdem die Frage, wie man einen bedingten Insert aufbaut, damit der nur durchgeführt wird, wenn ein Select() _keinen_ Datensatz geliefert hat.

Nun, du kannst eine COUNT()-Subquery stellen, die schonmal die Information liefert, ob was überlappt oder nicht. Die beïnhaltet alle drei(/vier) erwähnten Bedingungen.

Bekommt man das mit http://dev.mysql.com/doc/refman/5.1/de/insert-select.html hin?

INSERT INTO table SELECT * FROM (SELECT anfang, ende, sonstige Daten) dummyAlias WHERE (count-Subquery) = 0

Ich krieg es nicht hin. Vielleicht hat ja jemand anders eine gut Idee..

Zur Vereinfachung habe ich eine Tabelle zahl benutzt mit den Spalten id und nummer, in die ich eine neue Nummer eintragen will, wenn sie noch nicht vorhanden ist. Selbstverständlich hier NICHT mit einem Unique Index, da der ja für den anderen Anwendungsfall nicht nutzbar wäre.

Das zählen, wie oft die Nummer vorhanden ist, ist kein Problem.
Aber wie gestaltet man das Insert, wenn eben _kein_ Datensatz in den Filterbereich fällt, also count(*) = 0 ist.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Bekommt man das mit http://dev.mysql.com/doc/refman/5.1/de/insert-select.html hin?
INSERT INTO table SELECT * FROM (SELECT anfang, ende, sonstige Daten) dummyAlias WHERE (count-Subquery) = 0
Ich krieg es nicht hin. Vielleicht hat ja jemand anders eine gut Idee..
Aber wie gestaltet man das Insert, wenn eben _kein_ Datensatz in den Filterbereich fällt, also count(*) = 0 ist.

Wenn COUNT(*) = 0 zutrifft, dann ist die Bedingung erfüllt und das Subselect im FROM liefert seine Daten, die den neu einzufügenden Datensatz darstellen, an das INSERT. Wenn COUNT(*) = 0 nicht passt, gibt das außere SELECT eine leere Menge zurück => es wird nichts eingefügt.

INSERT INTO zahl SELECT * FROM (SELECT null, 42) dummyAlias WHERE (SELECT COUNT(*) FROM zahl WHERE nummer = 42) = 0

Man kann es auch ausführlicher schreiben, damit die Gemengelage der beiden ersten SELECTS klarer wird:

INSERT INTO zahl (id, nummer)
    SELECT id, nummer
    FROM
      (SELECT null AS id, 42 AS nummer) AS dummyAlias
    WHERE
      (SELECT COUNT(*) FROM zahl WHERE nummer = 42) = 0

Lo!

[Tom]

Hello,

Zur Vereinfachung habe ich eine Tabelle zahl benutzt mit den Spalten id und nummer, in die ich eine neue Nummer eintragen will, wenn sie noch nicht vorhanden ist. Selbstverständlich hier NICHT mit einem Unique Index, da der ja für den anderen Anwendungsfall nicht nutzbar wäre.

Schritt 1 wäre geschafft:

insert into zahl (zahl) select distinct 25 from zahl where 25 not in (select zahl from zahl);

Das funktioniert mit MySQL. Kann bestimmt noch vereinfacht werden, bevor man es jetzt umbaut auf den anderen Anwendungsfall...

Das zählen, wie oft die Nummer vorhanden ist, ist kein Problem.
Aber wie gestaltet man das Insert, wenn eben _kein_ Datensatz in den Filterbereich fällt, also count(*) = 0 ist.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

Zur Vereinfachung habe ich eine Tabelle zahl benutzt mit den Spalten id und nummer, in die ich eine neue Nummer eintragen will, wenn sie noch nicht vorhanden ist. Selbstverständlich hier NICHT mit einem Unique Index, da der ja für den anderen Anwendungsfall nicht nutzbar wäre.

Schritt 1 wäre geschafft:

insert into zahl (zahl) select distinct 25 from zahl where 25 not in (select zahl from zahl);

Das funktioniert mit MySQL. Kann bestimmt noch vereinfacht werden, bevor man es jetzt umbaut auf den anderen Anwendungsfall...

Die Terminverwaltung funktioniert so auch:

CREATE TABLE termin (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  start datetime NOT NULL,
  ende datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Terminkalender Test';

insert into termin (id, start, ende)
  select distinct null, '2010-11-01 09:00', '2011-11-02 14:00'
    from termin
    where (select count(*)
      from termin
      where ende >= '2010-11-01 09:00' and start <= '2010-11-02 14:00') = 0;

Die affected Rows geben dann Auskunft darüber, ob der Termin noch eingetragen werden konnte.

In der API müsste dann zuvor nur noch die Rahmenbedingung geprüft werden, dass ENDE > ANFANG sein muss.

Aber ohne das Distinct funktioniert meine Variante nicht...

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

insert into zahl (zahl) select distinct 25 from zahl where 25 not in (select zahl from zahl);

Deine Abfrage liefert zu viele (Zwischen-)Ergebnisse. DISTINCT ist nicht der beste Weg, sondern die Abfrage so zu gestalten, dass die gewünschte Anzahl herauskommt. Du fragst hier alle Reihen ab, in denen 25 nicht vorkommt. Das kann eine recht große Menge werden. Die interessiert dich aber gar nicht. Im Gegenteil, sie ist viel zu groß, weil du nur eine Ergebniszeile haben willst, und nicht so viele wie Datensätze in zahl vorhanden sind. Auf diese unsinnig große Menge lässt du dann DISTINCT los, was herauszufinden versucht, wieviele unterschiedliche Werte es gibt. Deswegen hab ich in meinem Vorschlag ein Subselect im FROM stehen, denn das liefert nur exakt einen Datensatz, der dann entweder genommen wird oder nicht.

Lo!

[Tom]

Hello,

insert into zahl (zahl) select distinct 25 from zahl where 25 not in (select zahl from zahl);

Deine Abfrage liefert zu viele (Zwischen-)Ergebnisse.

stimmt leider.

DISTINCT ist nicht der beste Weg, sondern die Abfrage so zu gestalten, dass die gewünschte Anzahl herauskommt. Du fragst hier alle Reihen ab, in denen 25 nicht vorkommt. Das kann eine recht große Menge werden. Die interessiert dich aber gar nicht. Im Gegenteil, sie ist viel zu groß, weil du nur eine Ergebniszeile haben willst, und nicht so viele wie Datensätze in zahl vorhanden sind. Auf diese unsinnig große Menge lässt du dann DISTINCT los, was herauszufinden versucht, wieviele unterschiedliche Werte es gibt. Deswegen hab ich in meinem Vorschlag ein Subselect im FROM stehen, denn das liefert nur exakt einen Datensatz, der dann entweder genommen wird oder nicht.

Dann also besser so für die Terminverwaltung:

$sql =
"insert into termin (id, start, ende)
 select null, '$start', '$ende'
    from (select null as id, '$start' as start, '$ende' as ende) as dummy
    where (select count(*)
      from termin
      where ende >= '$start' and start <= '$ende') = 0";

$start und $ende sind vrher ins passende Format zu bringen.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

Wichtig ist mir allerdings, das ich nur ungerne auf Klassen Dritter ausweichen möchte.

Wie Du sehen kannst, ist im Kern lediglich ein einziges SQL-Statement daraus geworden, noch nicht mal eine PHP-Funktion und schon gar keine Klasse.

Da Du dich aber nicht mehr beteiligt hast, ist es jetzt eine Lösung Dritter ;-P

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg