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

MySQL-Abfrage | Nur neueste, ungelesene Einträge darstellen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von wattfrass, 14 Februar 2016.

  1. wattfrass

    wattfrass Benutzer

    Hallo,

    ich benötige Unterstützung bei der nachfolgenden MySQL-Abfrage.

    Hierbei werden die ID´s (`candidate_author`.`candidateID`) sämtlicher Datensätze ausgegeben, bei welchen das Erstelldatum (`candidate_author`.`initial`) innerhalb der letzten 7 Tage liegt.

    Ferner werden die Seitenaufrufe für jeden Datensatz für jeden User berücksichtigt, welche in der Tabelle `candidate_database`.`candidate_visits` abgelegt werden. Es besteht eine 1:n Beziehung.

    Ziel ist es, nur die neuesten, ungelesenen Datensätze in einem Benachrichtigungsfeld darzustellen.

    Wurde der Datensatz von noch keinem Nutzer aufgerufen (d. h. `candidate_visits`.`accountID` IS NULL) generiert die Abfrage das gewünschte Ergebnis. Liegen allerdings bereits Einträge von anderen, als dem eingeloggten Benutzer vor (d. h. `candidate_visits`.`accountID` IS NOT NULL), werden auch Datensätze ausgegeben, die vom eingeloggten Nutzer angesehen wurden.

    Der Fehler liegt offenbar im rot-gekennzeichnetem Abschnitt. Anbei meine derzeitige Abfrage:


    SELECT `candidate_author`.`candidateID`,
    `candidate_author`.`initial`,
    `candidate_visits`.`accountID`,

    COUNT(*)

    FROM `candidate_database`.`candidate_author`

    LEFT JOIN `candidate_database`.`candidate_visits`
    ON `candidate_visits`.`candidateID` = `candidate_author`.`candidateID`

    WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) < `candidate_author`.`initial`

    AND ((`candidate_visits`.`accountID` IS NULL) OR (`candidate_visits`.`accountID` IS NOT NULL AND `candidate_visits`.`accountID` NOT LIKE '" . $accountID."'))

    GROUP BY `candidate_author`.`candidateID`

    ORDER BY `candidate_author`.`initial` DESC;



    Welche Maßnahmen sind erforderlich, dass sich das gewünschte Ergebnis erzielen lässt?

    Für jegliche Unterstützung bin ich dankbar.

    Viele Grüße und ein angenehmes Wochenende

    Wattfrass
     
  2. akretschmer

    akretschmer Datenbank-Guru

    zeig bitte auf das eigentliche Problem reduzierte Tabellen mit einigen wenigen Zeilen und was bei rauskommen soll. Dein syntaktisch kaputtes SQL (Aggregation und nicht alle anderen Spalten im Select gruppiert) ist aber schon mal definitv falsch.
     
  3. wattfrass

    wattfrass Benutzer

    Hallo,

    vielen Dank für die schnelle Antwort.

    Die Tabelle `candidate_author` besitzt die nachfolgende Struktur und beinhaltet das Erstelldatum des Datensatzes.

    === `candidate_database`.`candidate_author` ===
    candidateID; initial
    1; 2016-02-07 15:30:00
    2; 2016-02-06 14:30:00
    3; 2016-02-05 13:30:00

    Interpretation:
    Datensatz #1 wurde am 7.02.2016, 15:30 Uhr angelegt, usw.


    Die Tabelle `candidate_visits` enthält alle Aufrufe für oben aufgeführte Datensätze mit einer Zuordnung zum jeweiligen Benutzer (accountID).

    === `candidate_database`.`candidate_visits` ===
    candidateID; accountID; visitDate (Besuchsdatum)
    1; 1; 2016-02-07 15:45
    1; 1; 2016-02-08 12:00
    1; 2; 2016-02-08 14:00

    Interpretation:
    Datensatz #1 wurde von Benutzer 1 am 7.02.2016, 15:45 Uhr erstmalig aufgerufen.
    Datensatz #1 wurde von Benutzer 1 am 8.02.2016, 12:00 Uhr erneut aufgerufen.
    Datensatz #1 wurde von Benutzer 1 2 mal aufgerufen.
    Datensatz #1 wurde von Benutzer 2 am 8.02.2016, 14:00 Uhr erstmalig aufgerufen.
    Für die Datensätze 2 und 3 liegen keine Aufrufe vor, weder von Benutzer 1 noch 2.


    Ich bin als Benutzer 2 eingeloggt. Durch meine o.g. Abfrage werden alle 3 Datensätze ausgegeben.

    Datensatz #2 und #3 werden korrekt ausgegeben, da hier gar keine Einträge in `candidate_database`.`candidate_visits` enthalten sind. (`candidate_visits`.`accountID` IS NULL)

    Fälschlicherweise wird allerdings auch Eintrag #1 ausgegeben. Dieser wurde bereits von Benutzer 1 und Benutzer 2 aufgerufen, d.h. es liegen in candidate_visits Einträge für Benutzer 2 vor.

    Dies rührt daher, dass Benutzer 1 den Datensatz bereits betrachtet hat. `candidate_visits`.`accountID` NOT LIKE '" . $accountID."' zeigt nicht das gewünschte Ergebnis.

    Korrekt sollen nur die Datensätze #2 und #3 ausgegeben werden, für die keine Einträge in candidate_visits für den eingeloggten Benutzer (hier 2) vorliegen.

    Viele Grüße

    Wattfrass
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# select * from candidate_author;
     candidateid |  initial
    -------------+---------------------
      1 | 2016-02-07 15:30:00
      2 | 2016-02-06 14:30:00
      3 | 2016-02-05 13:30:00
    (3 Zeilen)
    
    test=*# select * from candidate_visits;
     candidateid | accountid |  visitdate
    -------------+-----------+---------------------
      1 |  1 | 2016-02-07 15:45:00
      1 |  1 | 2016-02-08 12:00:00
      1 |  2 | 2016-02-08 14:00:00
    (3 Zeilen)
    
    Wie sind die Tabellen verknüpft, was ist der Fremdschlüssel? Vom namen her candidateid, aber es steht geschrieben: "Die Tabelle `candidate_visits` enthält alle Aufrufe für oben aufgeführte Datensätze mit einer Zuordnung zum jeweiligen Benutzer (accountID)."

    Warum sehe ich keinen PK, zumindest nicht in der zweiten Tabelle?

    Verwirrt.
     
  5. wattfrass

    wattfrass Benutzer

    Hallo,

    die beiden Tabellen werden über den Fremdschlüssel `candidateID` verknüpft.

    Die Einträge der Tabelle `candidate_visits` beschreiben die Aufrufe der in Tabelle `candidate_author` entaltenen Datensätze nach Häufigkeit (Anzahl der Einträge; 1:n), Datum(visitDate) und Benutzer (accountID)

    Primärschlüssel der Tabelle `candidate_visits` ist `candidate_visits`.`id`. Vollständige Darstellung der Tabelle:

    === `candidate_database`.`candidate_visits` ===
    id; candidateID; accountID; visitDate (Besuchsdatum)
    1; 1; 1; 2016-02-07 15:45
    2; 1; 1; 2016-02-08 12:00
    3; 1; 2; 2016-02-08 14:00


    Viele Grüße

    Wattfrass
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# select * from candidate_author;
     candidateid |  initial
    -------------+---------------------
      1 | 2016-02-07 15:30:00
      2 | 2016-02-06 14:30:00
      3 | 2016-02-05 13:30:00
    (3 Zeilen)
    
    test=*# select * from candidate_visits ;
     candidateid | accountid |  visitdate
    -------------+-----------+---------------------
      1 |  1 | 2016-02-07 15:45:00
      1 |  1 | 2016-02-08 12:00:00
      1 |  2 | 2016-02-08 14:00:00
    (3 Zeilen)
    
    test=*# select * from candidate_author left join candidate_visits using (candidateid) where accountid is null;
     candidateid |  initial  | accountid | visitdate
    -------------+---------------------+-----------+-----------
      2 | 2016-02-06 14:30:00 |  |
      3 | 2016-02-05 13:30:00 |  |
    (2 Zeilen)
    
    So?
     
  7. wattfrass

    wattfrass Benutzer

    Die angegebene Abfrage hilft leider nur bedingt:

    "SELECT * FROM `candidate_database`.`candidate_author` LEFT JOIN `candidate_database`.`candidate_visits` USING (`candidateID`) WHERE `candidate_visits`.`accountID` IS NULL AND DATE_SUB(CURDATE(),INTERVAL 7 DAY) < `candidate_author`.`initial`"

    Für die oben dargestellte Situation ist die Ausgabe korrekt. Entfällt allerdings in Tabelle candidate_visits der Eintrag für Benutzer 2, bleibt die Ausgabe m.E. identisch. Korrekterweise müsste die Ausgabe allerdings für Benutzer 2 alle 3 Datensätze aus Tabelle
    candidate_author ergeben, für Benutzer 1 nur #2 und #3.

    candidateid | accountid | visitdate
    -------------+-----------+---------------------
    1 | 1 | 2016-02-07 15:45:00
    1 | 1 | 2016-02-08 12:00:00
    (2 Zeilen)

    In der Abfrage muss unweigerlich berücksichtigt werden, welcher Benutzer derzeit am System angemeldet ist.

    Viele Grüße

    Wattfrass
     
  8. akretschmer

    akretschmer Datenbank-Guru

    ich seh wohl nicht ganz durch. Wie ist der Benutzer angebildet? accountid?
     
  9. wattfrass

    wattfrass Benutzer

    Richtig, der angemeldete Benutzer wird über accountID abgebildet. Die ID kann mittels PHP über die Session ausgegeben werden.

    candidateid | accountid | visitdate
    -------------+-----------+---------------------
    1 | 1 | 2016-02-07 15:45:00
    1 | 1 | 2016-02-08 12:00:00

    candidateid | initial
    -------------+---------------------
    1 | 2016-02-07 15:30:00
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00

    Nach oben gezeigten Tabellenausschnitten sieht Benutzer 1 die Einträge #2 und #3.

    Benutzer 2 sieht hingegen alle 3 Einträge.


    Viele Grüße

    Wattfrass
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Code:
    test=*# select * from candidate_author left join candidate_visits using (candidateid) where accountid = 1;
     candidateid |  initial  | accountid |  visitdate
    -------------+---------------------+-----------+---------------------
      1 | 2016-02-07 15:30:00 |  1 | 2016-02-08 12:00:00
      1 | 2016-02-07 15:30:00 |  1 | 2016-02-07 15:45:00
    (2 Zeilen)
    
    test=*# select * from candidate_author left join candidate_visits using (candidateid) where accountid = 2;
     candidateid |  initial  | accountid |  visitdate
    -------------+---------------------+-----------+---------------------
      1 | 2016-02-07 15:30:00 |  2 | 2016-02-08 14:00:00
    (1 Zeile)
    
    Ich weiß nicht, ob ich Dich korrekt verstehe. Aber wenn Du auf DB-Ebene schon erreichen willst, daß User nur 'ihre' Rows sehen können/dürfen, dann wäre ein neues Feature in PostgreSQL 9.5 für Dich interessant: Row Level Security.

    Postgres 9.5 feature highlight: Row-Level Security and Policies
     
  11. wattfrass

    wattfrass Benutzer

    Nein, das ist es leider auch nicht.

    Für Account 1 müsste die Ausgabe wie folgt lauten:

    candidateid | initial
    -------------+---------------------
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00
    (2 Zeilen)

    Begründung:

    Account 1 hat Datensatz 1 bereits (2 mal) geöffnet. Datensatz 2 und 3 noch nicht. Siehe Tabelle candidate_visits:

    candidateid | accountid | visitdate
    -------------+-----------+---------------------
    1 | 1 | 2016-02-07 15:45:00
    1 | 1 | 2016-02-08 12:00:00
    (2 Zeilen)

    Für Account 2 müsste die Ausgabe wie folgt lauten:

    candidateid | initial
    -------------+---------------------
    1 | 2016-02-07 15:30:00
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00
    (3 Zeilen)

    Begründung:
    Account 2 hat keinen der 3 Datensätze geöffnet. Somit liegen keine Einträge für Benutzer 2 in der Tabelle candidate_visits vor.


    Viele Grüße

    Wattfrass
     
  12. akretschmer

    akretschmer Datenbank-Guru

    Das paßt nicht zur von Dir gezeigten Tabelle.
     
  13. wattfrass

    wattfrass Benutzer

    Für nachfolgende Tabelle candidate_visits:

    candidateid | accountid | visitdate
    -------------+-----------+---------------------
    1 | 1 | 2016-02-07 15:45:00
    1 | 1 | 2016-02-08 12:00:00
    1 | 2 | 2016-02-08 14:00:00
    (3 Zeilen)

    lauten für Account 1 und 2 die Ausgaben wie folgt:

    candidateid | initial
    -------------+---------------------
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00
    (2 Zeilen)

    Entfällt in der Tabelle candidate_visits der Eintrag für Benutzer 2, d. h.:

    candidateid | accountid | visitdate
    -------------+-----------+---------------------
    1 | 1 | 2016-02-07 15:45:00
    1 | 1 | 2016-02-08 12:00:00
    (2 Zeilen)

    Lautet für Account 1 die Ausgabe wie folgt:

    candidateid | initial
    -------------+---------------------
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00
    (2 Zeilen)

    Bei Account 2 werden alle 3 Einträge gezeigt.

    candidateid | initial
    -------------+---------------------
    1 | 2016-02-07 15:30:00
    2 | 2016-02-06 14:30:00
    3 | 2016-02-05 13:30:00
    (3 Zeilen)

    Wenn es nicht möglich ist, das gewünschte Ergebnis ohne Weiteres über eine MySQL-Abfrage zu realisieren, besteht alternativ die Möglichkeit m. H. v. PHP zu filtern.

    Viele Grüße

    Wattfrass
     
  14. akretschmer

    akretschmer Datenbank-Guru

    Wahrscheinlich stehe ich auf der Leitung. Ich geh jetzt rüber und mach den Platz für andere frei ;-)
     
  15. ukulele

    ukulele Datenbank-Guru

    Ich hab jetzt nur Post #3 wirklich gelesen, ist ja schon viel geschrieben worden :)

    Ich glaube dein Problem zu verstehen und denke das es so am besten beschreiben wird:
    Code:
    SELECT   a.*
    FROM   candidate_author a
    WHERE NOT EXISTS (   SELECT   1
               FROM   candidate_visits v
               WHERE   v.candidateID = a.candidateID
               AND     v.accountID = 2 )
    Natürlich kann man das auch mit einem Join ausdrücken:
    Code:
    SELECT   a.*
    FROM   candidate_author a
    LEFT JOIN candidate_visits v
    ON     v.candidateID = a.candidateID
    AND     v.accountID = 2
    WHERE   v.candidateID IS NULL
    Gruppieren, zählen, BenutzerID als Variable einsetzen ist ja erstmal nebensächlich.
     
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