mysql: Left Join an dieser Stelle?

[Jörg]

Hallo,

ich habe eine Query

SELECT
...
FROM tabelle_stunden std
LEFT JOIN tabelle_sonder s ON (std.User = s.User AND std.Datum BETWEEN s.Start AND s.End
AND std.User = '".$_POST['myUser']."') 
LEFT JOIN tabelle_mein m ON std.MID = m.MID
WHERE
std.User = '".$_POST['myUser']."'
AND std.Datum BETWEEN '".$_POST['datum_min']."' AND '".$_POST['datum_max']."'

Tabelle: stunden
ID, Datum, 

Tabelle sonder:
ID, Kategorie, Start,End

Tabelle mein: ID, MID, ...

Die funktioniert perfekt, aber listet mir nur die Tage auf, die auch in der Tabelle stunden vorhanden sind. Ich hätte aber gerne alle Tage aufgelistet, auch wenn sie in der Tabelle stunden gar nicht vorhanden ist.

Deshalb habe ich eie weitere Tabelle mit allen Daten bis ins Jahr 2100 angelegt.

Nun dachte ich, dass ich den SELECT auf diese Tabelle leite und alle anderen tabellen per LEFT JOIN einbinde. Aber ich erhalte trotzdem nur die in der tabelle stunden enthaltenen Daten. Vermutlich, weil ich über das Datum joinen muss.

Wie schaffe ich es, dass mir alle Tage im min-max-Zeitraum angezeigt werden und die entsprechenden Daten aus den anderen Tabellen dazu gejoint werden?

Jörg

[Rolf B]

Hallo Jörg,

SQL Queries operieren ausschließlich auf den Daten, die sie vorfinden.

Wenn der 03.10.2021 in keiner der Tabellen vorkommt, findet der Tag der Deutschen Einheit in deiner Liste nicht statt.

Ab MySQL8 oder MariaDB 10.2.1 könnte man es mit einer rekursiven CTE versuchen (WITH Befehl). Davor wirst Du Dir mit einer temporären Table behelfen müssen, die die benötigten Tage enthält. Dafür schreibst Du am besten eine Stored Procedure, denn darin kannst Du Schleifenbefehle verwenden:

delimiter //
create procedure createDays(anfang DATE, ende DATE)
begin
  drop temporary table if exists days;
  create temporary table days (datum date) engine memory;
  while (anfang <= ende) do
     insert into days(datum) values(anfang);
     set anfang = date_add(anfang, interval 1 day);
  end while;
end //
delimiter;

Die Delimiter-Story ist wichtig, weil Du sonst mit einen ; den create procedure beendest. Argh…

Den Namen für die Temp-Tabelle kannst Du nicht als Parameter verwenden. D.h. du kannst schon, müsstest dann in der Prozedur aber mit dynamischem SQL hantieren (PREPARE / EXECUTE), das könnte das Tempo wieder drosseln. Ich habe es nicht ausprobiert.

Wichtig ist auch "ENGINE MEMORY", andernfalls ist das laaaangsam. Oder Du stellst sicher, dass dein SQL Server für default_tmp_storage_engine die Einstellung "MEMORY" hat. Bei mir war's InnoDB und ich hab mich gewundert, warum er nur 30 Inserts pro Sekunde machte.

Wenn Du dann deine Query machst, rufst Du erstmal die Stored Procedure auf, um die days-Tabelle mit dem gewünschten Intervall zu befüllen, und kannst dann die days-Tabelle als führende Tabelle für deinen Join verwenden.

Gerne lasse ich mich von MYSQL Experten einer besseren Lösung belehren 😀

Rolf

[Jörg]

Hallo Rolf,

danke für Deine Antwort.

Wenn der 03.10.2021 in keiner der Tabellen vorkommt, findet der Tag der Deutschen Einheit in deiner Liste nicht statt.

Er kommt doch vor. Ich habe ja eine echte (also keine Temp-Tabelle) erstellt, die die Daten (also alle Tage) von 2000-2100 enthält.

Mir ist es nicht gelungen, sie so zu joinen, dass alle Daten (Datums) aus der Datum-Tabelle genommen werden und hierzu leftgejoint die restölichen Daten geNULLt oder eben verwendet werden.

Darauf bezog sich meine Frage.

Jörg

[Rolf B]

Hallo Jörg,

oh. Sorry 😀 - jetzt hast Du eine Zusatzlösung wie man die Tabelle auf der Fliege (on the fly) erstellt.

Hättest Du besser mal deine um diese Tabelle erweiterte Abfrage vorgestellt.

Du machst also jetzt so was?

SELECT d.datum, std.bla, s.fuu, m.baa
FROM datum_ref d
LEFT JOIN tabelle_stunden std ON std.datum = d.datum
LEFT JOIN tabelle_sonder s ON (std.User = s.User AND std.Datum BETWEEN s.Start AND s.End
AND std.User = '".$_POST['myUser']."') 
LEFT JOIN tabelle_mein m ON std.MID = m.MID

Das sollte aber funktionieren - bis auf das direkte Einsetzen von $_POST['myUser'] - das ist ein Kontextwechsel und du musst escapen.

Es ist natürlich so, dass für die Tage, die in deinen Tabellen nicht vorkommen, nur die d.datum Spalte gefüllt ist und der Rest NULL enthält; aber das wolltest Du ja so, oder?

Rolf

[Jörg]

Hallo Rolf,

oh. Sorry 😀 - jetzt hast Du eine Zusatzlösung wie man die Tabelle auf der Fliege (on the fly) erstellt.

👍😉

Hättest Du besser mal deine um diese Tabelle erweiterte Abfrage vorgestellt.

Ja,. denke ich im Nachhinein auch.

Du machst also jetzt so was?

SELECT d.datum, std.bla, s.fuu, m.baa
FROM datum_ref d
LEFT JOIN tabelle_stunden std ON std.datum = d.datum
LEFT JOIN tabelle_sonder s ON (std.User = s.User AND std.Datum BETWEEN s.Start AND s.End
AND std.User = '".$_POST['myUser']."') 
LEFT JOIN tabelle_mein m ON std.MID = m.MID

Ja, genau so hatte ichs mir vorgestellt.

Das sollte aber funktionieren - bis auf das direkte Einsetzen von $_POST['myUser'] - das ist ein Kontextwechsel und du musst escapen.

Hätte ich auch gedacht. Umso besser wärs gewesen, wenn ich die nicht funktionierende Query gepostet hätte. Escapen ist auch klar, das mache ich, bevors in den Produktivbetrieb geht.

Es ist natürlich so, dass für die Tage, die in deinen Tabellen nicht vorkommen, nur die d.datum Spalte gefüllt ist und der Rest NULL enthält; aber das wolltest Du ja so, oder?

Ganz genauso wollte ich es.

Ich schraub nochmal daran herum,. wenns dann wieder nicht funktioniert, komme ich mal mit der Querty um die Ecke.

Jörg

[Jörg]

Hallo Rolf,

Du machst also jetzt so was?

SELECT d.datum, std.bla, s.fuu, m.baa
FROM datum_ref d
LEFT JOIN tabelle_stunden std ON std.datum = d.datum
LEFT JOIN tabelle_sonder s ON (std.User = s.User AND std.Datum BETWEEN s.Start AND s.End
AND std.User = '".$_POST['myUser']."') 
LEFT JOIN tabelle_mein m ON std.MID = m.MID

Ja, genau so hatte ichs mir vorgestellt.

Und jetzt habe ichs umgesetzt und habe wieder denselben Effekt wie heute schonmal. Ich bekomme die Daten nicht durchgängig angezeigt, sondern nur die Daten, die auch in der stunden-Tabelle stehen.

Das ist doch dann kein LEFT JOIN. 😕

Woran kanns also liegen?

Jörg

[Rolf B]

Hallo Jörg,

ich hab grad mal angefangen, Testtabellen aufzubauen.

Test 1: Datum und Stunden.

LEFT JOIN ergibt zuerst alle Rows, wo die Stunden-Table nicht null ist. Die null-Rows dahinter.

Der Optimizer hat also offenbar beschlossen, primär über die Stundentabelle zu laufen und die Datumstabelle zuzumischen. Das mag an der Verteilung der Datenzeilen liegen (1000 Zeilen für 3 Jahre und 3 Zeilen in den Stunden). Jedenfalls brauchte ich einen ORDER BY um die Datümer in die richtige Ordnung zu bringen.

Ist das bei Dir vielleicht auch so? Und Du hast so viele befüllte Datenzeilen, dass die Null-Zeilen außer Sicht sind?

Wenn sie echt fehlen: ich würde so vorgehen wie ich im phpmyadmin (oder was Du verwendest) Schritt für Schritt aufbauen. Erst nur die Datums-Tabelle, dann die Stunden dazu, dann die Sonder-Tabelle.

Eine Frage nebenbei: Wenn Du die Abfrage auf std.User in die ON-Bedingung für tabelle_sonder hängst, dann bekommst Du (solltest Du bekommen) die tabelle_stunden-Einträge für alle User und nur die Sonder-Einträge werden auf den genannten User limitiert. Ist das das, was Du willst?

Zuerst die Datu Rolf

[Jörg]

Hallo Rolf,

ich hab grad mal angefangen, Testtabellen aufzubauen.

Kannst Du mir mal nen Dump Deiner Tabellen geben?

Test 1: Datum und Stunden.

LEFT JOIN ergibt zuerst alle Rows, wo die Stunden-Table nicht null ist. Die null-Rows dahinter.

Der Optimizer hat also offenbar beschlossen, primär über die Stundentabelle zu laufen und die Datumstabelle zuzumischen. Das mag an der Verteilung der Datenzeilen liegen (1000 Zeilen für 3 Jahre und 3 Zeilen in den Stunden). Jedenfalls brauchte ich einen ORDER BY um die Datümer in die richtige Ordnung zu bringen.

Ist das bei Dir vielleicht auch so? Und Du hast so viele befüllte Datenzeilen, dass die Null-Zeilen außer Sicht sind?

Nein, ich habe ja auf einen Monat reduziert, daher kann ich mir alle Zeilen anschauen.

Wenn sie echt fehlen: ich würde so vorgehen wie ich im phpmyadmin (oder was Du verwendest) Schritt für Schritt aufbauen. Erst nur die Datums-Tabelle, dann die Stunden dazu, dann die Sonder-Tabelle.

Ich verwende auch phpmyadmin. Und genauso habe ichs versucht. Einfach nur einen einzigen LEFT JOIN auf die Stunden. Hat schon nicht funktioniert.

Eine Frage nebenbei: Wenn Du die Abfrage auf std.User in die ON-Bedingung für tabelle_sonder hängst, dann bekommst Du (solltest Du bekommen) die tabelle_stunden-Einträge für alle User und nur die Sonder-Einträge werden auf den genannten User limitiert. Ist das das, was Du willst?

Ich limitiere eigentlich 2 x auf den User. 1x in der on-Bedingung und 1x in der WHERE-Bedingung der Query.

SELECT
    STD.Datum,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    a.Datum = STD.Datum
WHERE
    STD.Datum BETWEEN '2021-04-01' AND '2021-04-30' AND STD.User='xyz'
ORDER BY
    STD.Datum ASC

Und ich fürchte, genau zweitere lässt meine Query meine Ergebnismange so reduzieren.

Jörg

[Rolf B]

Hallo Jörg,

Kannst Du mir mal nen Dump Deiner Tabellen geben?

Das bringt kaum was - eine Tabelle mit Datümern vom 1.1.2020 bis 31.12.2022 und eine Mini-Tabelle aus datum, user und Stundenzahl mit 3 Einträgen drin.

Aber - Mist - den WHERE hab ich übersehen. Das hatte ich als Teil der ON Bedingung gelesen.

Ein WHERE wird nach den JOINs durchgeführt und schmeißt deswegen die Rows raus, wo der User NULL ist.

Filtere den User mal vorne, beim Join von alletage und stunden. Dann wird das nur auf die Stundentabelle angewendet.

Den Datums-Bereich kannst Du im WHERE belassen. Er müsste aber auch als Teil der ON-Bedingung funktionieren; ggf. sogar performanter, aber das müsste man messen bzw. mit EXPLAIN untersuchen. Das kann ich bei mir nicht, meine DB ist zu klein und mein MySQL wird deshalb anders optimieren.

SELECT
    STD.Datum,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    a.Datum = STD.Datum AND STD.Datum
    AND STD.User='xyz'
WHERE
    a.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    STD.Datum ASC

Beim Zumischen der Sonder-Tabelle brauchst keinen Vergleich des Users mit den $_POST Daten. Denke ich jedenfalls. Denn da hast Du ja eh schon std.user = s.user im ON.

Rolf

[Jörg]

Hallo Rolf,

Filtere den User mal vorne, beim Join von alletage und stunden. Dann wird das nur auf die Stundentabelle angewendet.

Habe ich versucht, leider ohne Erfolg:

    SELECT
    STD.Datum,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum AND STD.User = 'xyz'
    )
WHERE
    STD.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    STD.Datum ASC

ergibt bei mir immer noch erst Daten ab dem 06.04.2021. 😟

    SELECT
    STD.Datum,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum
    )
WHERE
    STD.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    STD.Datum ASC

ergibt alle Daten ab dem 01.04.2021, aber dann eben nicht mehr für meinen User "xyz", sondern über alle User hinweg.

Beim Zumischen der Sonder-Tabelle brauchst keinen Vergleich des Users mit den $_POST Daten. Denke ich jedenfalls. Denn da hast Du ja eh schon std.user = s.user im ON.

Ja, das sehe ich genauso.

Aber es gelingt mir leider immer noch nicht, meinen LeftJoin wirksam zu erzeugen. Kenne ich so gar nicht 😟

Jörg

[MudGuard]

Hi,

Filtere den User mal vorne, beim Join von alletage und stunden. Dann wird das nur auf die Stundentabelle angewendet.

Habe ich versucht, leider ohne Erfolg:

    SELECT
    STD.Datum,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum AND STD.User = 'xyz'
    )
WHERE
    STD.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    STD.Datum ASC

mach mal die Datums-Einschränkung im WHERE auf alletage, nicht auf stunden.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

yup - so schrub ich es gestern abend. Aber beim Lesen von Jörgs Posting ist es mir auch nicht aufgefallen. Unglaublich, welche Lapsūs (sic!)^[1] einem alle unterlaufen können.

Rolf

---

1. Wie Status: Der Plural ist lapsus mit langem U, weder lapsi noch lapsen oder lapsusse... ↩︎

[Jörg]

Hallo Rolf,

yup - so schrub ich es gestern abend. Aber beim Lesen von Jörgs Posting ist es mir auch nicht aufgefallen. Unglaublich, welche Lapsūs (sic!)[^1] einem alle unterlaufen können.

Und mir ist es in Deinem Post nicht aufgefallen 😉 Schön, dass wir dem Lapus mit vereinten Kräften den Garaus gemacht haben. ☺️

Jörg

[MudGuard]

Hi,

Schön, dass wir dem Lapus mit vereinten Kräften den Garaus gemacht haben. ☺️

Ist das jetzt Rekursion? Ein Lapsus beim Lapsus? 😉

cu,
Andreas a/k/a MudGuard

[Jörg]

Ist das jetzt Rekursion? Ein Lapsus beim Lapsus? 😉

Genau darum ging es mir 😜😉

Noch eine Frage:

SELECT
    s.Kategorie,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum AND STD.User = 'xyz'
    )
LEFT JOIN _436sdw_zz_sonder s ON
    (
        STD.User = s.User AND a.Datum BETWEEN s.Start AND s.End
    )
WHERE
    a.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    `a`.`Datum` ASC

hat noch ein kleines Problem, es setzt mir grundsätzlich die s.Kategorie auf NULL, auch wenn zu einem Datum ein Eintrag für den User in der Tabelle "sonder" besteht, der innerhalb von Start und End liegt.

Jörg

[Jörg]

hat noch ein kleines Problem, es setzt mir grundsätzlich die s.Kategorie auf NULL, auch wenn zu einem Datum ein Eintrag für den User in der Tabelle "sonder" besteht, der innerhalb von Start und End liegt.

Das konnte ich aber über

SELECT
    s.Kategorie,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum AND STD.User = 'xyz'
    )
LEFT JOIN _436sdw_zz_sonder s ON
    (
       a.Datum BETWEEN s.Start AND s.End AND STD.User = 'xyz'
    )
WHERE
    a.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    `a`.`Datum` ASC

lösen.

Jörg

[Rolf B]

Hallo Jörg,

Das konnte ich aber über ... lösen

Hm. So langsam beginne ich, den Überblick zu verlieren - bzw. ich habe ihn offenbar noch nie gehabt. Denn mir fällt jetzt ein möglicher Defekt auf.

Du hast die Datümer in alledaten.

Da mischst Du mit LEFT JOIN Sätze hinzu, die zu einem bestimmten User gehören. Aus der Stunden-Tabelle die vom Datum, aus der Sonder-Tabelle die, deren Datumsintervall das Datum einschließt.

Heißt doch eigentlich: Zwischen Stunden und Sonder gibt es überhaupt keinen Bezug - außer der User-ID. Aber das könnte zu wenig sein.

Wenn man deine Daten nicht kennt, ist es ja so, dass Du zu einem Datum x Sätze in der Stundentabelle und y Sätze in der Sondertabelle finden kannst. x und y sind Zahlen von 0 bis N. In dem Moment, wo x>1 UND y>1 wird, bekommst Du alle möglichen Kombinationen dieser Sätze geliefert.

Fall 1: Das ist okay so und deine Auswertung kommt damit klar.

Fall 2: Es ist nicht okay, aber dann musst Du die Garantie haben, dass x>1 UND y>1 nicht zutreffen kann. Ist diese Voraussetzung gegeben? Gibt es zu einem User und Tag maximal einen Stunden-Eintrag? Oder zu einem User und Intervall maximal einen Sonder-Eintrag? Ist vielleicht sogar generell x>1 ODER y>1 ausgeschlossen?

Fall 3: Es ist nicht okay, und du kannst die Garantie nicht geben. In dem Fall musst Du überlegen, wie Du damit umgehst.

Rolf

[Jörg]

Hallo Rolf,

Hm. So langsam beginne ich, den Überblick zu verlieren - bzw. ich habe ihn offenbar noch nie gehabt. Denn mir fällt jetzt ein möglicher Defekt auf.

Da ist definitiv noch etwas nicht so, wie gewünscht. Denn der LEFT JOIN zur sonder-Tabelle gibt mir teilweise noch nicht die korrekte Kategorie aus.

Du hast die Datümer in alledaten.

Ja.
ID(int,autoincremnt)
Datum(date)
Wochentag(varchar)

Da mischst Du mit LEFT JOIN Sätze hinzu, die zu einem bestimmten User gehören. Aus der Stunden-Tabelle die vom Datum, aus der Sonder-Tabelle die, deren Datumsintervall das Datum einschließt.

Genau.

Heißt doch eigentlich: Zwischen Stunden und Sonder gibt es überhaupt keinen Bezug - außer der User-ID. Aber das könnte zu wenig sein.

User-ID und ggf. Datum, wenn es zwischen Start und End liegt.

Wenn man deine Daten nicht kennt, ist es ja so, dass Du zu einem Datum x Sätze in der Stundentabelle und y Sätze in der Sondertabelle finden kannst. x und y sind Zahlen von 0 bis N. In dem Moment, wo x>1 UND y>1 wird, bekommst Du alle möglichen Kombinationen dieser Sätze geliefert.

Ok. Deshalb an dieser Stelle:
X kann 0, 1 oder n sein.
Y kann auch 0,1 oder n sein.

Fall 1: Das ist okay so und deine Auswertung kommt damit klar.

Wenn die Ergebnismenge aus meiner Abfrage stimmen würde, würde meine Auswertung damit klar kommen (müssen).

Ich hadere also doch immer noch mir meiner Query.

Edit an dieser Stelle (manchmal hilft das Schreiben eines Post bei der Lösung - hier wieder geschehen):

Ich habe den LEFTJOIN der Sondertabelle wieder falsch gemacht, auch dort muss ich auf den User dieser Tabelle Bezug nehmen!!

Also:

SELECT
    s.Kategorie,
    a.Datum
FROM
    alletage a
LEFT JOIN stunden STD ON
    (
        a.Datum = STD.Datum AND s.User = 'xyz'
    )
LEFT JOIN _436sdw_zz_sonder s ON
    (
       a.Datum BETWEEN s.Start AND s.End AND STD.User = 'xyz'
    )
WHERE
    a.Datum BETWEEN '2021-04-01' AND '2021-04-30'
ORDER BY
    `a`.`Datum` ASC

Das scheint mir von der Abfrage her das gewünschte Ergebnis zu bringen.

....

Habs gerade nochmal in den Echtdaten ausprobiert, die Ergebnismenge sieht sehr aus, wie mein gewünschtes Ergebnis ☺️

Siehst du denn noch irgendwo einen möglichen Defekt?

Jörg

[Rolf B]

Hallo Jörg,

Siehst du denn noch irgendwo einen möglichen Defekt?

Ja - wollte ich eben schon aufgeschrieben haben, ist mir aber scheinbar untergegangen. Du fragst im ON der Sonder-Tabelle STD.User statt S.user ab. Das kann nicht stimmen. Bzw. es würde dazu führen (denke ich), dass Du nur dann Sondersätze bekommst, wenn es einen Satz in der Stundentabelle gibt.

Die Frage, was Du mit dem Fall tun willst, wenn Du 2 Stundensätze und 2 Sondersätze zu einem Datum hast, ist Dir also noch total unklar? Denn die Query liefert Dir dann 4 Ergebniszeilen.

Stunden:

Datum	User	bla
03.05.2021	Rolf	17
03.05.2021	Rolf	22
04.05.2021	Rolf	42

Sonder:

Start	Ende	User	Kategorie	blub
01.01.2021	10.05.2021	Rolf	Kat1	99
01.05.2021	31.05.2021	Rolf	Kat2	47

Ergibt

Datum	User	bla	Start	Ende	Kategorie	blub
03.05.2021	Rolf	17	01.01.2021	10.05.2021	Kat1	99
03.05.2021	Rolf	22	01.01.2021	10.05.2021	Kat1	99
03.05.2021	Rolf	17	01.05.2021	31.05.2021	Kat2	47
03.05.2021	Rolf	22	01.05.2021	31.05.2021	Kat2	47
04.05.2021	Rolf	42	01.01.2021	10.05.2021	Kat1	99
04.05.2021	Rolf	42	01.05.2021	31.05.2021	Kat2	47

Da sind etliche Daten mehrfach vorhanden. Willst Du die dann auch mehrfach in deiner Auswertung anzeigen? Den "Vorgang" finde ich in deinen Tabellenbeschreibungen bisher nicht, deswegen weiß ich nicht wie der ins Spiel kommt. Ist das die Kategorie?

Rolf

[Jörg]

Hallo Jörg,

Siehst du denn noch irgendwo einen möglichen Defekt?

Ja - wollte ich eben schon aufgeschrieben haben, ist mir aber scheinbar untergegangen. Du fragst im ON der Sonder-Tabelle STD.User statt S.user ab. Das kann nicht stimmen. Bzw. es würde dazu führen (denke ich), dass Du nur dann Sondersätze bekommst, wenn es einen Satz in der Stundentabelle gibt.

Ganz kurz an dieser Stelle, auf den Rest antworte ich gesondert:

Aaahh!!! 🤬

Verteufelt, ich hatte den fehler schon bemerkt:

Ich habe den LEFTJOIN der Sondertabelle wieder falsch gemacht, auch dort muss ich auf den User dieser Tabelle Bezug nehmen!!

Ich habe dann aber das s innerhalb des Quellcodes fett machen woillen, damit der Unterschied überhaupt auffällt. Das geht hier aber nicht (leider), deshalb hab ichs wieder rückgängig gemacht und damit die aölte (falsche) Query wieder hergestellt.

Insofern: Ja, dieser Fehler war in der Tat noch vorhanden (jedoch schon bemerkt).

Jetzt schau ich mir mal den Rest des Posts an 😉

Jörg

[Jörg]

Hallo Rolf,

Da sind etliche Daten mehrfach vorhanden. Willst Du die dann auch mehrfach in deiner Auswertung anzeigen? Den "Vorgang" finde ich in deinen Tabellenbeschreibungen bisher nicht, deswegen weiß ich nicht wie der ins Spiel kommt. Ist das die Kategorie?

Ok, jetzt weiß ich, was Du meinst.
Sorry für das pfennigweise Fallen dieses Groschens 😉

Naja, das ist teilweise gewollt.
Aber eben wirklich nur teilweise.

Die Einträge aus der Stunden-Tabelle müssen tatsächlich alle aufgeführt werden.

Die anderen sind eher störend.

Bevor ich jetzt hier weiter Zeit investiere, gehe ich mal Deiner parallel eingetroffenen Antwort nach und überlege, ob diese Alternative sinnvoller ist.

Ich schreibe dann dort weiter, wo dann auch die Reise hin geht, also entweder wieder hier oder dort. ;)

Dir vielen Dank für Deine Hilfe! 👍

Jörg

[Jörg]

Hallo Andreas a/k/a MudGuard,

mach mal die Datums-Einschränkung im WHERE auf alletage, nicht auf stunden.

Jaaa! 👍

Jetzt läufts! Das war das letzte fehlende (bzw. falsche) Puzzleteilchen. Und ich erhalte genau das Ergebnis, das ich möchte und auch über den LeftJoin erwartet hätte.

Vielen Dank an Dich und Rolf! 👍

Ich habe jetzt zwar noch eine Frage zur grundsätzlichen Problemstellunmg und Programmiertechnik, die mache ich aber in einem Teilstrang dieses Themas auf.

Jörg

[Jörg]

Hallo,

nachdem ich nun meine Daten aus der DB in php vorliegen habe und mit diesen ein wenig rechnen muss, stellt sich mir die Frage nach der sinnvollsten Programmiertechnik.

Zur Ausgangslage:

Ich möchte eine Tabelle ausgeben, die tageweise, vorgangsbezogene Werte ausgibt.

Also:

Dazu habe ich ja grundsätzlich wenigstens 2 Alternativen:

1. Ich sammle und verarbeite zweilenweise meine Daten, bis sich der Vorgang oder das Datum ändert und schreibe dann meine Zeile.

2. So habe ich es mopmentan angefangen: Ich sammle und verarbeite Daten und speichere sie in einem mehrdimensionalen Array ala $myArray[$Datum][$VorgangsID]['Unterpunkt123'] und nach Durchlaufen der while-Schleife der Ergebnismenge habe ich in meinem Array alle nötigen Werte. Anschließend löse ich das Array nach $Datum und innerhalb dessen nach $VorgangsID auf und schreibe hieurbei meine Tabelle.

Frage: Ist Punkt 2 unnötiges Hin- und Herkopieren von Daten? Es erscheint mir auf den ersten Blick etwas übersichtlicher zu handeln für mich.

Jörg

[Rolf B]

Hallo Jörg,

aber unterschlägst Du jetzt nicht was? Nämlich den Umstand, dass es Datumswerte ohne Vorgang gibt. Denn das war ja vorhin noch Thema und Trigger für den Thread.

Wie gibst Du so was aus?

Aber damit hätten wir das Thema besser eröffnet, denn dann wäre uns mutmaßlich der Ärger mit der alledaten Tabelle erspart geblieben. Wenn es nur darum ging, nicht vorhandene Datumswerte als "kein Eintrag" in dieser Liste aufzunehmen, hätte man das einfacher lösen können.

Bevor ich hier auf Abgleich und Gruppenwechsel eingehe, hätte ich diese Voraussetzung gerne geklärt.

Rolf

[Jörg]

Hallo Rolf,

aber unterschlägst Du jetzt nicht was? Nämlich den Umstand, dass es Datumswerte ohne Vorgang gibt. Denn das war ja vorhin noch Thema und Trigger für den Thread.

Richtig, die gibt es.

Wie gibst Du so was aus?

Die hatte ich mit im Array geplant. $myArray[$Datum][0][0], also quasi eine Art "Fakeeintrag".

Aber damit hätten wir das Thema besser eröffnet, denn dann wäre uns mutmaßlich der Ärger mit der alledaten Tabelle erspart geblieben. Wenn es nur darum ging, nicht vorhandene Datumswerte als "kein Eintrag" in dieser Liste aufzunehmen, hätte man das einfacher lösen können.

Einverstanden. Nur ist es natürlich so, dass man nicht jedes Thema der Entwicklung zuerst einmal ins Forum einbringt, sondern man fängt an und stösst ggf. auf ein Problem, wo man nicht weiter kommt. Und dann entwickelt sich das Thema und man merkt, dass es hierzu bessere Lösungsansätze gibt (wie jetzt?).

Bevor ich hier auf Abgleich und Gruppenwechsel eingehe, hätte ich diese Voraussetzung gerne geklärt.

Ich bin gerne bereit, auf einen besseren Ansatz, gerne auch ohne die alleDatenTabelle umzusatteln.

Jörg

[Rolf B]

Hallo Jörg,

Nur ist es natürlich so, dass man nicht jedes Thema der Entwicklung zuerst einmal ins Forum einbringt, sondern man fängt an und stösst ggf. auf ein Problem, wo man nicht weiter kommt.

Ja, ich weiß schon. Das klassische XY-Problem. X ist zu lösen, man hat ein Detailproblem Y und sucht dafür nach Hilfe. Dabei könnte man Y ganz vermeiden.

Ich würde es vermutlich mit zwei Queries lösen und für den User die Stundensätze im gewünschten Datumsintervall lesen sowie die Sondersätze, deren Start/End Intervall das gewünschte Datumsintervall überschneidet. Die Tabelle "mein" haben wir aus dem Auge verloren, die schreib ich jetzt einfach mal dazu und hoffe, dass sie keine zusätzliche Komplikation bringt.

SELECT datum, std, bla1, bla2
FROM   stunden std
   LEFT JOIN 
       tabelle_mein m ON std.MID = m.MID
WHERE user = $user AND datum BETWEEN $von AND $bis
ORDER BY datum

SELECT start, end, blub1, blub2
FROM   sonder s
WHERE user = $user AND start <= $bis AND end >= $von
ORDER BY datum

Zur Erklärung der "Überschneiden sich die Intervalle" Abfrage: Es gibt 6 mögliche Lagen von start/end und von/bis. Wenn man genau hinguckt, ist es dann "bad", wenn start > bis ODER end < von. Invertiert man das, ergibt sich mit der De Morgan Regel die obige Abfrage.

start <= end < von                 <= bis                   bad
start       <= von          <= end <= bis                   good
start       <= von                 <= bis <  end            good
               von <  start <= end <= bis                   good
               von <  start        <= bis <  end            good
               von <               <= bis <  start <= end   bad

Im Ergebnis hast Du zwei Arrays. Eins mit den Stunden pro Tag, eins mit den Sonder-Intervallen für den gewünschten Zeitraum. Die Datumsangaben stehen da - meine ich - im Format yyyy-mm-dd drin.

Das Problem, wie man das darstellen muss, ist mir unklar. Das ist deine Fachlichkeit.

Aber grundsätzlich würde ich nun die Datümer im PHP generieren, und nicht in der DB.

$datum = new DateTime($_POST['von']);
$bis = new DateTime($_POST['bis']);
$einTag = new DateTimeInterval("P1D");

while ($datum < $bis) {
   $cDatum = $datum->format("Y-m-d");

   // Verarbeitung

   $datum->add($einTag);
}

Ob das mit dem new DateTime so einfach ist, hängt von dem Format ab, in dem Du das Datum vom Client bekommst. Ggf. musst Du DateTime::createFromFormat verwenden.

Wie die Verarbeitung aussieht, hängt nun von deiner Fachlichkeit ab. Wenn Du pro Datum über die Vorgänge gruppieren willst, müsstest Du die nun herausfinden. Und dann pro Vorgang die Ausgaben durchführen. Dazu suchst Du Dir die entsprechenden Einträge aus den Arrays heraus. Das kann man alles ordentlich auf Funktionen verteilen und strukturieren.

Rolf

[Jörg]

Hallo Rolf,

mal ganz zu Anfang eine Rückfrage:

SELECT start, end, blub1, blub2
FROM   sonder s
WHERE user = $user AND start <= $bis AND end >= $von
ORDER BY datum

Schön wärs, wenn wir hier ein datum hätten, wir haben aber nur Start und End.

Im Ergebnis hast Du zwei Arrays. Eins mit den Stunden pro Tag, eins mit den Sonder-Intervallen für den gewünschten Zeitraum. Die Datumsangaben stehen da - meine ich - im Format yyyy-mm-dd drin.

Das tun sie.

Dazu suchst Du Dir die entsprechenden Einträge aus den Arrays heraus. Das kann man alles ordentlich auf Funktionen verteilen und strukturieren.

Bin ich denn wirklich so viel schneller, wenn ich mir zu einem Datum YYYY-mm-dd den Kategorieeintrag aus dem 2.Array heraus suche als in der db selber? Oder sehe ich hier den Trick nicht, wie man das effizient macht?

Jörg

[Jörg]

Nochmal ich,

Bin ich denn wirklich so viel schneller, wenn ich mir zu einem Datum YYYY-mm-dd den Kategorieeintrag aus dem 2.Array heraus suche als in der db selber? Oder sehe ich hier den Trick nicht, wie man das effizient macht?

Ehrlich gesagt weiß ich auch nicht genau, wie ich schnell und direkt auf Einträge meines ersten arrays zugreife, wenn ich über die Tage in php iteriere. ich könnte mir das für das erste Array vorstellen, wenn ich das Ergebnisarray aus sql zuvor entsprechend umkopiere und das Datum als Key verwende.

Fpr das zweite Array fällt mir auf Anhieb gar keine schnelle Lösung mit Direktzugriff ein.

Oder ist mir einfach nicht bekannt, wie man sowas macht? 😕 Bringt ja nichts, für jedes Datum aus php beide Arrays jeweils zu durchlaufen, oder?

Jörg

[Rolf B]

Hallo Jörg,

Ehrlich gesagt weiß ich auch nicht genau, wie ich schnell und direkt auf Einträge meines ersten arrays zugreife, wenn ich über die Tage in php iteriere.

Mach Dir nicht so viel Kopf um drei Millisekunden. Diese Zugriffszeiten sind nur bei richtig großen Arrays relevant, oder wenn Du einen Server hast, der mit vielen Usern an der Leistungsgrenze rennt.

Schreib's erstmal so, dass es funktioniert und verständlich ist. Dann miss die Laufzeit. Und überleg Dir das Mengengerüst für die Zugriffe. Wie oft passiert das? Durch wie viele Leute? Davon hängt doch ab, ob der Server deine Last überhaupt spürt oder nicht.

Rolf

[Jörg]

Hallo Rolf,

Mach Dir nicht so viel Kopf um drei Millisekunden. Diese Zugriffszeiten sind nur bei richtig großen Arrays relevant, oder wenn Du einen Server hast, der mit vielen Usern an der Leistungsgrenze rennt.

Schreib's erstmal so, dass es funktioniert und verständlich ist. Dann miss die Laufzeit. Und überleg Dir das Mengengerüst für die Zugriffe. Wie oft passiert das? Durch wie viele Leute? Davon hängt doch ab, ob der Server deine Last überhaupt spürt oder nicht.

Du hast völlig recht. ich habs jetzt mal durchprogrammiert und fertige für jeden Tag 2 Array an:

$arrVorgang[$Datum][$VorgangsID]['myKey'] = ...  
$arrTag[$Datum][myKey] = ...

In das erstere packe ich alle Vorgangsdaten (mit vernünftigen Keys), die mir später wichtig sind, in das zweite alle Tagesdaten, die sich über die Vorgänge (oder auch über Abfragedaten) ergeben.

Hierbei habe ich Deine beiden Queries umgesetzt.

Da ich mir später noch Gedanken über eine sinnvolle Zuordnung der Daten aus der Tabelle "sonder" machen wollte, habe ich diese nun erstmal einfach als function inkl. der Query hinterlegt, anstelle sie in ein php-Array zu packen.

Aber selbst so läuft das Script für 1 User in 1 Monat durch, wie nix. Soll heißen, ich klicke und das ergebnis ist da, getestet auf dem Produktivserver mit Produktivdaten. Bin mir inzwischen nicht mal mehr sicher, ob ich das nochmal ändern soll, was die Tabelle "sonder" und die 30 Queries angeht, die ja durch mein Provisorium entstehen.

Das Einzige, was ein bischen ungünstig ist, ist, dass auch mehrere Kategorien aus der Tabelle "sonder" für einen Tag gelten können. Aber zum einen läßt sich das hierarchisch deutlich abmildern und die Fälle, die übrig bleiben, sind 4 an der Zahl. Deshalb implodiere ich dann das Rückgabearray der Funktion und werte diese 4 Varianten zusätzlich zu den Einzelvarianten aus.

Nochmal vielen dank für Deine Hilfe,

Jörg

[Rolf B]

Hallo Jörg,

Schön wärs, wenn wir hier ein datum hätten

Kopierfehler meinerseits. Eine Sortierung ist, wenn Du die Einträge im PHP aus dem Array raussuchst und dem Datum zuordnest, vielleicht gar nicht mehr nötig. Das ist jetzt eher die Frage, was man fachlich braucht. Vielleicht nach dem Start-Datum sortieren.

Bin ich denn wirklich so viel schneller, wenn ich mir zu einem Datum YYYY-mm-dd den Kategorieeintrag aus dem 2.Array heraus suche

Die DB muss es ja auch suchen. Unterm Strich muss der Aufwand also auf jeden Fall getrieben werden.

Ein in-memory Search in einem Array ist aber immer schnell genug, sofern Du keinen Blödsinn programmierst. Die Stunden-Einträge kannst Du assoziativ ablegen, d.h. ein Array auf Stufe 1, dessen Schlüssel der Datumsstring ist, und darin ein Array mit den Stunden-Rows für dieses Datum. Das ist sozusagen Direktzugriff. Sowas hast Du ja jetzt schon.

Da kannst Du auch so vorgehen, dass Du dieses Array auch als Leitstruktur verwendest. D.h. bevor Du die DB liest, erzeugst Du pro Datum einen Eintrag:

$stunden[$cDatum] = ARRAY();

und hängst die Stunden-Rows an das passene Datum an, so dass Du nachher nur noch mit foreach ($stunden as $cDatum => $stundenEinträge) drüberlaufen musst.

Die Sondereinträge sind da schon schwieriger, weil Du Einträge finden musst, wo das Datum in einem Intervall liegt. D.h. du kannst da nur ein Array aus den Sonder-Rows machen und das sequenziell durchlaufen. Natürlich kann man noch Tricks mit Datenstrukturen einsetzen, um das zu beschleunigen, aber da stellt sich die Frage nach dem Aufwand. Wenn Du nur zehn Sonder-Rows für eine Auswertung hast, lohnt sich das nicht. Das wird eher ab 1000 Sonder-Rows interessant.

Beim Programmieren achtet man zuerst auf Verständlichkeit und Einfachheit. Nur, wenn man damit in Zeitprobleme kommt, baut man komplexere Strukturen, die ggf. laufzeiteffizienter sind.

Rolf