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

[wattfrass]

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

 

[akretschmer]

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.

 

[wattfrass]

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

 

[akretschmer]

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.

 

[wattfrass]

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

 

[akretschmer]

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?

 

[wattfrass]

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

 

[akretschmer]

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

 

[wattfrass]

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

 

[akretschmer]

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

 

[wattfrass]

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

 

[akretschmer]

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.

Das paßt nicht zur von Dir gezeigten Tabelle.

 

[wattfrass]

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

 

[akretschmer]

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

 

[ukulele]

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:

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:

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.