SQL Abfrage erweitern

[Ghost109]

Guten Abend zusammen !


ich bin mir nicht sicher, ob dieses Thema eher ein Datenbank oder PHP Thema ist - falls es hier falsch sein sollte, dann bitte verschieben, danke !


Ich habe eine Datenbank, in welche ich meine belegten Termine in die Tabelle "events" speichere.
Sieht in etwa so aus:

Bildschirmfoto 2021-06-09 um 19.13.00.png


Hier steht wann der Termin beginnt (Datum / Uhrzeit) und welcher Service zu diesem Termin gehört.
Mit Hilfe dieser SQL Query lasse ich mir alle möglichen Termine anzeigen, von heute bis + 30 Tage, Zeitfenster 14 - 18 Uhr, abzüglich meiner o.g. belegten Termine = nur noch freie Termine.

SELECT 
DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as freetime
FROM 
seq_0_to_30 AS day, 
seq_14_to_18 AS hour
HAVING freetime NOT IN ( SELECT `start` FROM `events`)

Hier ein Ausschnitt:

Bildschirmfoto 2021-06-09 um 19.13.43.png



Das funktioniert auch ganz gut - jetzt möchte ich meine Abfrage erweitern.
Ich bräuchte unterm Strich 2 unterschiedliche Abfragen:

Wenn der Benutzer auf den Button "Service 1" oder "Service 2" klickt, soll folgende Logik greifen:
-> Zeige mir alle freien Termine
--> Existiert an dem Tag bereits ein belegter Termin mit service 3, dann dürfen die vorherigen Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen


Wenn der Benutzer auf den Button "Service 3" klickt, soll folgende Logik greifen:
-> Zeige mir alle freien Termine
--> Existiert an dem Tag bereits ein belegter Termin mit service 3, dann dürfen die nachfolgenden Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen


Ich hoffe ihr könnt mir folgen.
Finde das Thema mehr als nur komplex. Vielleicht ist es das ... vielleicht stelle ich es mir auch nur schwerer vor als gedacht. Aber ich hoffe Ihr könnt mir helfen.

Sollte etwas unklar sein, dann fragt gerne. Bin über jede Unterstützung dankbar !

[rkr]

Ne, ist kein PHP-Thema.

Kannst du mal hier eine ein paar Beispieldaten vorbereiten und ggf. mal deinen Gedankengang dazu erklären?
Das ist nicht besonders aufwändig. Aber ich muss wissen, wo ich dich da abholen kann.

[Thallius]

Ich frage mich ob es wirklich Sinn macht das die Datenbank alleine machen zu lassen. Ich würde das mit php mit einer Matrix lösen und mir aus der dB nur die belegten Termine holen.

[rkr]

Ich frage mich ob es wirklich Sinn macht das die Datenbank alleine machen zu lassen. Ich würde das mit php mit einer Matrix lösen und mir aus der dB nur die belegten Termine holen.

Kommt drauf an, wie viele Termine das einmal werden. Und ich denke nicht, dass eine Lösung mit PHP hier eleganter wäre.

[Thallius]

Kommt drauf an, wie viele Termine das einmal werden. Und ich denke nicht, dass eine Lösung mit PHP hier eleganter wäre.

Hat er doch geschrieben. 30 Tage a 4 Stunden. Also gerade mal 120 Einträge. Und vlt. ist es nicht eleganter aber wahrscheinlich deutlich einfacher zu verstehen. Was nutzt es ein super komplexes query zu generieren, dass dann nur 1/100 aller Programmierer kapieren und u.u. Später erweitern oder ändern können? Wartungsfreundlicher Code bedeutet für mich auch, dass man es nicht komplexer macht als eben notwendig nur weil man es kann….

[rkr]

Hat er doch geschrieben. 30 Tage a 4 Stunden. Also gerade mal 120 Einträge. Und vlt. ist es nicht eleganter aber wahrscheinlich deutlich einfacher zu verstehen. Was nutzt es ein super komplexes query zu generieren, dass dann nur 1/100 aller Programmierer kapieren und u.u. Später erweitern oder ändern können? Wartungsfreundlicher Code bedeutet für mich auch, dass man es nicht komplexer macht als eben notwendig nur weil man es kann….

So komplex wird es dieses Mal nicht.
Mich würde aber auch eine Gegenüberstellung von der SQL-Lösung und einer PHP-Lösung interessieren.

[protestix]

Finde das Thema mehr als nur komplex. Vielleicht ist es das ...

Ja ist es.

Du speicherst zwar wann ein Termin anfängt aber nicht wann er aufhört oder sind alle immer gleich lang und wenn ja wie lange?

[Ghost109]

protestix
Doch, es gibt auch eine Spalte End. Termine dauern immer 1h - sollte aber für die Aufgabenstellung nicht relevant sein, denke ich.

@rkr
Stelle die Daten gleich zur Verfügung.

Warum das ganze?
Ich möchte einfach, dass bestimme freie Zeitfenster nicht angezeigt wird, je nach dem, welchen Service Button der User geklickt hat

https://www.db-fiddle.com/f/67PBWueNtDNvAepkdX7fkx/0

[rkr]

Irgs. Kannst du dein Datumszeug gerade noch auf DATETIME umbauen?

[rkr]

So, kurz Zeit...

Datumsangaben sollten in ein DATETIME oder ein TIMESTAMP.

Ich habe dein Schema etwas angepasst.

Sehe ich das richtig, dass "Freie Termine" immer in vollen Stundenfenstern liegen? Also beispielsweise keine 30-Minutenscheiben?

DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)

Das braucht man nicht, wenn man die Sequenz von 0-30 auf 1-31 anpasst.

Und damit das in DB-Fiddle funktioniert, da DB-Fiddle leider nur MySQL, aber nicht MariaDB unterstützt, habe ich die Sequenz gerade mal angepasst:

SELECT
    DATE_ADD(DATE_ADD(DATE_ADD(DATE(NOW()), INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as timeslot
FROM
    (SELECT 1 AS seq UNION SELECT 2 AS seq UNION SELECT 3 AS seq UNION SELECT 4 AS seq UNION SELECT 5 AS seq UNION SELECT 6 AS seq UNION SELECT 7 AS seq UNION SELECT 8 AS seq UNION SELECT 9 AS seq UNION SELECT 10 AS seq UNION SELECT 11 AS seq UNION SELECT 12 AS seq UNION SELECT 13 AS seq UNION SELECT 14 AS seq UNION SELECT 15 AS seq UNION SELECT 16 AS seq UNION SELECT 17 AS seq UNION SELECT 18 AS seq UNION SELECT 19 AS seq UNION SELECT 20 AS seq UNION SELECT 21 AS seq UNION SELECT 22 AS seq UNION SELECT 23 AS seq UNION SELECT 24 AS seq UNION SELECT 25 AS seq UNION SELECT 26 AS seq UNION SELECT 27 AS seq UNION SELECT 28 AS seq UNION SELECT 29 AS seq UNION SELECT 30 AS seq UNION SELECT 31 AS seq) AS day,
    (SELECT 14 AS seq UNION SELECT 15 AS seq UNION SELECT 16 AS seq UNION SELECT 17 AS seq UNION SELECT 18 AS day_offset) AS hour

Du kannst später dieses SELECT ... UNION-Geraffel wieder zu seq_X_to_Y zurückbauen.

Let's go.

Was du durch diese Abfrage bekommst, sind alle Timeslots, nicht nur die freien Timeslots. Das können wir als Basis nehmen.

Wenn der Benutzer auf den Button "Service 1" oder "Service 2" klickt, soll folgende Logik greifen:
-> Zeige mir alle freien Termine
--> Existiert an dem Tag bereits ein belegter Termin mit service 3, dann dürfen die vorherigen Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen

Wenn ein Service-3 auf 16 Uhr liegt, dann darf 16-17 Uhr aber auch nicht als freier Slot angezeigt werden, oder?

Es gibt mehrere Wege das zu lösen. Der einfachste ist wahrscheinlich Folgender: Da das Zeitfenster max 5 Stunden breit ist (14:00 - 18:59 Uhr), kann man doch einfach für die Betrachtung was bereits belegt ist, auf den aktuellen Slot-Zeitpunkt als Startzeitpunkt und den Slot-Zeitpunkt plus 4 Stunden, 59 Minuten als Endzeitpunkt gehen. Joinen wir also die Events-Tabelle an die generierten Timeslots, dann können wir für jeden Timeslot fragen, ob es einen Termin

BETWEEN DATE_SUB(timeslot, INTERVAL 4 HOUR) AND DATE_ADD(timeslot, INTERVAL 59 MINUTE)

gibt, der

service=3

ist. Wenn die Slots immer eine Stunde breit sind, dann braucht man nur den Startzeitpunkt eines bereits gebuchten Events.

Dafür brauche ich zunächst aber mal den Timeslot als fertig berechnetes Feld. Also so:

SELECT
    timeslot
FROM (
        SELECT
            DATE_ADD(DATE_ADD(DATE_ADD(DATE(NOW()), INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as timeslot
        FROM
            (SELECT 1 AS seq UNION SELECT 2 AS seq UNION SELECT 3 AS seq UNION SELECT 4 AS seq UNION SELECT 5 AS seq UNION SELECT 6 AS seq UNION SELECT 7 AS seq UNION SELECT 8 AS seq UNION SELECT 9 AS seq UNION SELECT 10 AS seq UNION SELECT 11 AS seq UNION SELECT 12 AS seq UNION SELECT 13 AS seq UNION SELECT 14 AS seq UNION SELECT 15 AS seq UNION SELECT 16 AS seq UNION SELECT 17 AS seq UNION SELECT 18 AS seq UNION SELECT 19 AS seq UNION SELECT 20 AS seq UNION SELECT 21 AS seq UNION SELECT 22 AS seq UNION SELECT 23 AS seq UNION SELECT 24 AS seq UNION SELECT 25 AS seq UNION SELECT 26 AS seq UNION SELECT 27 AS seq UNION SELECT 28 AS seq UNION SELECT 29 AS seq UNION SELECT 30 AS seq UNION SELECT 31 AS seq) AS day,
            (SELECT 14 AS seq UNION SELECT 15 AS seq UNION SELECT 16 AS seq UNION SELECT 17 AS seq UNION SELECT 18 AS day_offset) AS hour
    ) t

Wenn der Benutzer auf den Button "Service 3" klickt, soll folgende Logik greifen:
-> Zeige mir alle freien Termine
--> Existiert an dem Tag bereits ein belegter Termin mit service 3, dann dürfen die nachfolgenden Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen

Dann joinst du noch mal left gegen die Tabelle t und und machst das gleiche noch mal für die nachfolgenden Termine.

Am Ende gruppierst du nach dem timeslot und zählst die Datensätze von dem ersten und zweiten Join. Mehr als 0? Timeslot geblockt.

Proof of concept: https://www.db-fiddle.com/f/67PBWueNtDNvAepkdX7fkx/1

[Ghost109]

Guten Morgen,

entschuldige bitte die späte Rückmeldung, war im Stress

Wenn ein Service-3 auf 16 Uhr liegt, dann darf 16-17 Uhr aber auch nicht als freier Slot angezeigt werden, oder?

Doch, danach sollen Service 1+2 möglich sein, nur im Vorfeld nicht.

Und ja, die TimeSlots sind immer 1h lang

[rkr]

Kannst du ja dann entsprechend anpassen:

SELECT
    t.timeslot AS timeslot_start,
    DATE_ADD(t.timeslot, INTERVAL 1 HOUR) AS timeslot_end,
    IF(COUNT(e_serviceA.id) > 0, 'blocked', null) AS service12,
    IF(COUNT(e_serviceB.id) > 0, 'blocked', null) AS service3
FROM (
        SELECT
            DATE_ADD(DATE_ADD(DATE_ADD(DATE('2021-06-09'), INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as timeslot
        FROM
            seq_0_to_30 AS day, seq_14_to_18 AS hour
    ) t
LEFT JOIN
    events e_serviceA ON t.timeslot BETWEEN DATE_SUB(e_serviceA.start, INTERVAL 4 HOUR) AND DATE_ADD(e_serviceA.start, INTERVAL 59 MINUTE) AND e_serviceA.service=3
LEFT JOIN
    events e_serviceB ON t.timeslot BETWEEN e_serviceB.start AND DATE_ADD(e_serviceB.start, INTERVAL 239 MINUTE) AND e_serviceB.service=3
GROUP BY
    t.timeslot
ORDER BY
    t.timeslot 

[Ghost109]

ES TUT MIR SO LEID !
Ich glaube ich habe Mist bei der Beschreibung der Situation gebaut - ich hoffe dass eine Anpassung des jetzt Lösungsansatz nicht zu Aufwändig ist


Korrektur:
Wenn der Benutzer auf den Button "Service 1" oder „Service 3“ klickt, soll folgende Logik greifen:

• Rufe mir alle möglichen freien Termine ab
  • Existiert an dem Tag bereits ein belegter Termin mit „Service 2“, dann dürfen die vorherigen Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen

Wenn der Benutzer auf den Button "Service 2" klickt, soll folgende Logik greifen:

• Rufe mir alle möglichen freien Termine ab
  • Existiert an dem Tag bereits ein belegter Termin mit "Service 1“ oder „Service 3“, dann dürfen die nachfolgenden Zeitfenster dieses Tags nicht als "freie Termine" zur Verfügung stehen

Lässt sich das so auch realisieren? :/

[rkr]

Lässt sich das so auch realisieren? :/

Ja, du musst an der Abfrage oben nur ein paar Werte ändern.

[Ghost109]

habe es nun versucht anzupassen, bin mir aber nicht sicher, ob es richtig ist

SELECT
    t.timeslot AS timeslot_start,
    IF(COUNT(e_serviceA.id) > 0, 'blocked', null) AS service13,
    IF(COUNT(e_serviceB.id) > 0, 'blocked', null) AS service2
FROM (
        SELECT
            DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL day.seq DAY), INTERVAL hour.seq HOUR) as timeslot
        FROM
            seq_0_to_30 AS day, seq_14_to_18 AS hour
    ) t
LEFT JOIN
    events e_serviceA ON t.timeslot BETWEEN DATE_SUB(e_serviceA.start, INTERVAL 4 HOUR) AND DATE_ADD(e_serviceA.start, INTERVAL 59 MINUTE) AND e_serviceA.service=2
LEFT JOIN
    events e_serviceB ON t.timeslot BETWEEN e_serviceB.start AND DATE_ADD(e_serviceB.start, INTERVAL 239 MINUTE) AND e_serviceB.service=2
GROUP BY
    t.timeslot
ORDER BY
    t.timeslot

Habe dein DATE('2021-06-09') durch Current_Date() ersetzt und die Zeile DATE_ADD(t.timeslot, INTERVAL 1 HOUR) AS timeslot_end entfernt.
Denke die war für die "Logik" nicht notwendig, korrekt?
Ebenso habe ich die service "Zahlen" geändert.