Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Lieferfähigkeit prüfen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von HolgerH, 23 Juli 2015.

  1. HolgerH

    HolgerH Benutzer

    Hey alle zusammen,

    ich hoffe, ihr könnt mir helfen, denn ich bin mit meinem Latein am Ende...

    Für eine Verwaltung habe ich eine Seite mit der Übersicht von Bestellungen.
    Soweit so gut, nun brauch ich noch "Filter". Ich möchte z.B. filtern können: Zeige mir alle lieferbaren Bestellungen an. In der normalen Übersicht erledigt das live meine PHP Klasse und gibt das grafisch aus.
    Aber als richtiger Filter taugt das ja nichts, da dann meine Paginierungs Klasse nicht mehr hinhaut, wenn man was "unter den Tisch fallen lässt" :)

    Also, muss es schon in der DB Abfrage gefiltert werden.
    Um es knapp zu halten, nur das wichtigste:

    Spalten der Tabelle "bestellungen":
    b_id

    Spalten der Tabelle "bestell_positionen":
    bp_id
    bp_artikel_id
    bp_bestell_id

    Spalten der Tabelle "lagerplaetze"
    lp_id
    lp_artikel_id
    lp_anzahl

    Meine gekürzte Abfrage um die Übersicht der Bestellungen auszugeben (Tabelle "lagerplaetze" wird noch nicht verwendet):

    SELECT
    bestellungen.b_id,
    bestell_positionen.bp_id,
    COUNT(bestell_positionen.bp_id) AS positions
    FROM
    bestellungen
    JOIN bestell_positionen
    ON bestellungen.b_id = bestell_positionen.bp_bestell_id
    GROUP BY bestellungen.b_id

    Das funktioniert soweit alles wie es soll...

    Wie muss die Abfrage aussehen, wenn er mir nur Bestellungen zeigen soll, deren Bestellpositionen alle lieferbar sind?
    Mein Problem ist dabei: In einer Bestellung kann es ja 10 oder mehr Bestellpositionen (Artikel) geben.
    Davon muss dann ja noch jeder einzlene Bestellposition geprüft werden, ob auf den Lagerplätzen (eine Bestellposition kann auf mehreren Lagerplätzen vorhanden sein) genug da ist.
    Ich stehe da echt auf dem Schlauch, denn Filtern würde ich gerne:
    Zeige mir alle "lieferbaren" Bestellungen
    Zeige mir alle "teilweise" lieferbaren Bestellungen
    Zeige mir alle Bestellungen, die nicht lieferbar sind

    Die "live" Ampel (durch PHP Klassen gebaut) funktioniert in der Ausgabe der Übersicht einwandfrei. Aber als SQL Statement schaffe ich das einfach nicht.

    Oder ist das u.U. auch garnicht möglich was ich da vorhabe?

    Bin für jede Anregung dankbar.

    Vielen Dank und LG,
    Holger

    PS: sicherlich kann ich die Ausgabe durch meine PHP Klassen ganz einfach manipulieren und die "gefilterten" Bestellungen einfach überspringen. Aber dann stimmt meine Blätterfunktion nicht mehr. Ich bekomme 50 Ergebnisse pro Seite geliefert, 15 fliegen durch meine Filter Klassen raus bleiben nur 35 zum Anzeigen,
    aber die Blätterfunktion sagt logisch 1 - 50 usw.
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Nein, das ist syntaktisch falsch. Alle Spalten des Resultates müssen entweder aggregiert oder gruppiert sein.
     
  3. akretschmer

    akretschmer Datenbank-Guru

    Möglich mit Sicherheit. Wenn ich mal Zeit habe, könnte ich das mit PostgreSQL mal zeigen, MySQL nutze ich aus Prinzip nicht.
    Aber vermutlich wird meine Lösung bei Dir dann nicht 1:1 funktionieren.
     
  4. HolgerH

    HolgerH Benutzer

    Ok, ich schaue auch schon nach alternativen Möglichkeiten. Eine weitere Spalte in die Tabelle "bestellungen" z.B. "live_status".
    Und bei jeder Lagerbewegung die Lieferfähigkeit neuer Bestellungen durch meine PHP Klassen abgleichen zu lassen.
    Das ist zwar mehr Aufwand, aber da bin ich einfach fitter, als in komplexen SQL Statements :)

    Mal noch aus Neugierde, wie müsste denn meine Abfrage syntaktisch korrekt aussehen?
    Immerhin funktioniert sie ja einwandfrei und liefert mir auch die passende Menge an Bestellpositionen pro Bestellung.
     
  5. akretschmer

    akretschmer Datenbank-Guru

    wie ich schrieb, alle Spalten aggregieren oder gruppieren, ALLE! In zukünftigen Versionen von MySQL wird es nicht mehr so gehen, derzeit ist MySQL aber blind und liefert Dir ein Ergebniss, welches zufällig richtig sein kann, aber nicht muß.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Mal als Fingerübung:

    Code:
    test=*# \d artikel
      Table "public.artikel"
     Column |  Type  | Modifiers
    --------+---------+-----------
     id  | integer | not null  
     name  | text  |   
    Indexes:   
      "artikel_pkey" PRIMARY KEY, btree (id)
    Referenced by:   
      TABLE "bestand" CONSTRAINT "bestand_artikel_id_fkey" FOREIGN KEY (artikel_id) REFERENCES artikel(id)
      TABLE "positionen" CONSTRAINT "positionen_artikel_fkey" FOREIGN KEY (artikel) REFERENCES artikel(id)
    
    test=*# \d lager
      Table "public.lager"
     Column |  Type  | Modifiers
    --------+---------+-----------
     id  | integer | not null  
     name  | text  |   
    Indexes:   
      "lager_pkey" PRIMARY KEY, btree (id)
    Referenced by:
      TABLE "bestand" CONSTRAINT "bestand_lager_id_fkey" FOREIGN KEY (lager_id) REFERENCES lager(id)
    
    test=*# \d bestand
      Table "public.bestand"
      Column  |  Type  | Modifiers
    ------------+---------+-----------
     lager_id  | integer | not null
     artikel_id | integer | not null
     menge  | integer |
    Indexes:
      "bestand_pkey" PRIMARY KEY, btree (lager_id, artikel_id)
    Foreign-key constraints:
      "bestand_artikel_id_fkey" FOREIGN KEY (artikel_id) REFERENCES artikel(id)
      "bestand_lager_id_fkey" FOREIGN KEY (lager_id) REFERENCES lager(id)
    
    test=*# \d bestellung
      Table "public.bestellung"
     Column |  Type  | Modifiers
    --------+---------+-----------
     id  | integer | not null
     name  | text  |
    Indexes:
      "bestellung_pkey" PRIMARY KEY, btree (id)
    Referenced by:
      TABLE "positionen" CONSTRAINT "positionen_bestellung_fkey" FOREIGN KEY (bestellung) REFERENCES bestellung(id)
    
    test=*# \d positionen
      Table "public.positionen"
      Column  |  Type  | Modifiers
    ------------+---------+-----------
     id  | integer | not null
     bestellung | integer |
     artikel  | integer |
     anzahl  | integer |
    Indexes:
      "positionen_pkey" PRIMARY KEY, btree (id)
    Foreign-key constraints:
      "positionen_artikel_fkey" FOREIGN KEY (artikel) REFERENCES artikel(id)
      "positionen_bestellung_fkey" FOREIGN KEY (bestellung) REFERENCES bestellung(id)
    
    test=*#
    
    Und diesen Daten

    Code:
    test=*# select * from artikel ;
     id | name   
    ----+------   
      1 | art1   
      2 | art2   
      3 | art3   
    (3 rows)   
    
    Time: 0,160 ms
    test=*# select * from lager ;
     id |  name
    ----+--------
      1 | lager1
      2 | lager2
      3 | lager3
    (3 rows)
    
    Time: 0,158 ms
    test=*# select * from bestand ;
     lager_id | artikel_id | menge
    ----------+------------+-------
      1 |  1 |  10
      1 |  3 |  5
      2 |  2 |  12
      2 |  3 |  8
      3 |  1 |  2
    (5 rows)
    
    Time: 0,167 ms
    test=*# select * from bestellung ;
     id |  name
    ----+----------
      1 | bestell1
      2 | bestell2
      3 | bestell3
    (3 rows)
    
    Time: 0,157 ms
    test=*# select * from positionen ;
     id | bestellung | artikel | anzahl
    ----+------------+---------+--------
      1 |  1 |  1 |  2
      2 |  1 |  2 |  2
      3 |  2 |  1 |  1
      4 |  2 |  2 |  100
      5 |  3 |  1 |  1
      6 |  3 |  3 |  1
    (6 rows)
    
    bekommst so eine Auflistung aller Bestellpositionen und ob lieferbar oder nicht:

    Code:
    test=*# with artikelbestand as (select b.artikel_id as artikel, sum(menge) as anzahl from bestand b left join artikel a on b.artikel_id = a.id left joinlager l on b.lager_id=l.id group by artikel_id), bestellpositionen as (select b.id, p.artikel, sum(anzahl) as anzahl from bestellung b left join positionen p on b.id=p.bestellung group by b.id, p.artikel) select b.id, b.artikel, b.anzahl, a.anzahl, case when b.anzahl <= a.anzahl then 'lieferbar' else 'nicht im Bestand' end as status from bestellpositionen b left join artikelbestand a on b.artikel = a.artikel order by b.id;
     id | artikel | anzahl | anzahl |  status
    ----+---------+--------+--------+------------------
      1 |  1 |  2 |  12 | lieferbar
      1 |  2 |  2 |  12 | lieferbar
      2 |  2 |  100 |  12 | nicht im Bestand
      2 |  1 |  1 |  12 | lieferbar
      3 |  1 |  1 |  12 | lieferbar
      3 |  3 |  1 |  13 | lieferbar
    (6 rows)
    
    Dies liefert Dir alle Bestellungen, die lieferbar wären (count-Spalte = 0)

    Code:
    test=*# select id, count(*) filter (where status = 'nicht im Bestand') from (with artikelbestand as (select b.artikel_id as artikel, sum(menge) as anzahl from bestand b left join artikel a on b.artikel_id = a.id left join lager l on b.lager_id=l.id group by artikel_id), bestellpositionen as (select b.id,p.artikel, sum(anzahl) as anzahl from bestellung b left join positionen p on b.id=p.bestellung group by b.id, p.artikel) select b.id, b.artikel, b.anzahl, a.anzahl, case when b.anzahl <= a.anzahl then 'lieferbar' else 'nicht im Bestand' end as status from bestellpositionen b left join artikelbestand a onb.artikel = a.artikel order by b.id) foo group by id;
     id | count
    ----+-------
      1 |  0
      2 |  1
      3 |  0
    
    Also, Bestellung 1 und 3 wären lieferbar.


    Man muß natürlich aufpassen: wenn man was ausliefert, verringert sich wieder der Bestand, da kann es also passieren, ich liefere Bestellung X aus und dann ist Bestellung Y plötzlich nicht mehr lieferbar.

    Kann man alles natürlich a bissl lesbarer gestalten, das ist mir nur so grad auf die Tastatur gefallen...
     
  7. Distrilec

    Distrilec Datenbank-Guru

    Grundstäzlich gesagt: Die Abfragen sind alle nicht ganz korrekt...
    Was ist wenn du erst in zwei Wochen liefern musst, derzeit noch nicht genug Bestand vorhanden ist aber nächste Woche nocheinmal genug für die Lieferung produziert wird? :)
     
  8. HolgerH

    HolgerH Benutzer

    @Distrilec: Ok, das ist kein Problem! Dafür soll ja die Prüfung der Lieferfähigkeit erfolgen.
    Grundsätzlich kann nur das ausgeliefert werden, was auch vorhanden ist. Daher suchte ich ja nach einer Lösung, sich die lieferbaren Bestellungen zu filtern.
    Gefiltert wird zusätzlich auch nach Bestelleingang. Also wer zuerst kommt, malt auch zuerst.

    @akretschmer: Wow, wenn dir das "nur mal gerade so" auf die Tastatur gefallen ist, alle Achtung!
    Da muss ich mich nun erst einmal "durchkämpfen" um das zu alles verinnerlichen... Hab jedenfalls tausend Dank für deine ganze Mühe!
    Wenn ich das so sehe, hab ich da wohl noch echten Nachholbedarf, was SQL angeht :)
     
  9. Distrilec

    Distrilec Datenbank-Guru

    Aus welcher Sicht soll die Übersicht den kommen? Aus Versand-Sicht ? Denn da gibt es dann ganz andere Probleme als Beispielsweise in der Auftragsplanung... :)
    Als Anreiz: Zwei Bestellungen für den gleichen Artikel, am gleichen Tag... Du hast aber nur Bestand für eine Bestellung... Was tun?
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Die Syntax ist einklich SQL-Standard (mit WITH und so), aber MySQL ist zu weit weg von diesen Standards um das zu verstehen. Also mit diesem Spielzeug wird das so erst einmal nicht gehen.
     
  11. HolgerH

    HolgerH Benutzer

    Korrekt, das ist die Sicht des Konfektionierers. Der soll filtern können, was noch gepackt werden kann. Aus der
     
  12. HolgerH

    HolgerH Benutzer

    Sicht der AV ist das kein Problem, denn die können einzlnen Bestellungen eine Prio mitgeben, oder die ganze Bestellung in eine Art Warteschleife schieben.
     
  13. HolgerH

    HolgerH Benutzer

    Hab in diesem Forum das Problem, das es meine Antworten nicht speichern mag. :-(
    Egal, welcher Browser. Der Balken oben rechts "rödelt" wie blöde, aber nichts passiert...
     
  14. Distrilec

    Distrilec Datenbank-Guru

    Du brauchst für dein Vorhaben fotlaufende Summen.... Als kann ich dir sagen, dass du es mit MySQL nicht hinbekommen wirst...
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden