Tabelle nur nach den aktuellsten Einträgen filtern / ganze Zeile gesucht / GROUP BY?

[myscy]

Hallo zusammen,

ich arbeite an einem Werkzeugverleih-Modell und komme so gar nicht weiter. Vielleicht kann mir einer der Profis hier helfen und einen Tipp geben.

In meiner Tabelle "lending" werden alle laufenden und abgeschlossenen Leihen gespeichert.

id | toolId | userId | lendingDate | ... | lendingStatus | ...
--------------------------------------------------------------
1  | 1      | 11     | 1234567890  | ... | 3             | ...
2  | 1      | 13     | 1234567891  | ... | 3             | ...
3  | 1      | 17     | 1234567892  | ... | 1             | ...  <- neuester Eintrag für toolId=1
4  | 2      | 23     | 1234567801  | ... | 3             | ...
5  | 2      | 29     | 1234567805  | ... | 1             | ...  <- neuester Eintrag für toolId=2
6  | 3      | 31     | 1234567897  | ... | 2             | ...  <- neuester Eintrag für toolId=3
...

Ich möchte von jedem einzelnen Werkzeug ("toolId") den jeweils aktuellsten Eintrag (Spalte "lendingDate" speichert Timestamp) haben.

Es soll pro Werkzeug eine ganze Zeile ausgegeben werden:

id | toolId | userId | lendingDate | ... | lendingStatus | ...
--------------------------------------------------------------
3  | 1      | 17     | 1234567892  | ... | 1             | ...  <- neuester Eintrag für toolId=1
5  | 2      | 29     | 1234567805  | ... | 1             | ...  <- neuester Eintrag für toolId=2
6  | 3      | 31     | 1234567897  | ... | 2             | ...  <- neuester Eintrag für toolId=3
...

Mit "GROUP BY toolID" bekomme ich zwar eine komplette Liste mit alle Werkzeugen hin, aber die Werte in den Spalten (bis auf toolId) gehören nicht zur Zeile mit dem höchsten "lendingDate"-Wert

SELECT * FROM mysqldb.lending GROUP BY toolId;

id | toolId | userId | lendingDate | ... | lendingStatus | ...
--------------------------------------------------------------
1  | 1      | 11     | 1234567890  | ... | 3             | ...
4  | 2      | 23     | 1234567801  | ... | 3             | ...
6  | 3      | 31     | 1234567897  | ... | 2             | ......

Ich habe schon recherchiert aber stehe immer noch auf dem Schlauch

Hat jemand eine Idee?

Kriegt man das mit Subabfragen (SELECT ... FROM (SELECT ...)) oder einem (RIGHT) JOIN hin?

Verglichen mit einem Kunde-Bestellungen Szenario wäre das, glaube ich, eine Liste der jeweils aktuellsten/letzten Bestellung eines jeden Kunden.

Danke im Voraus!!


Hier noch der DDL-Eintrag meiner Tabelle:

CREATE TABLE `lending` (
  `id` int NOT NULL AUTO_INCREMENT,
  `toolId` int DEFAULT NULL,
  `userId` int DEFAULT NULL,
  `lendingDate` bigint DEFAULT NULL,
  `dueDate` bigint DEFAULT NULL,
  `returnedDate` bigint DEFAULT NULL,
  `lendingStatus` int DEFAULT NULL,
  `timestamp` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_lendingStatus_idx` (`lendingStatus`),
  KEY `fk_toolId_idx` (`toolId`),
  KEY `fk_userId_idx` (`userId`),
  CONSTRAINT `fk_lendingStatus` FOREIGN KEY (`lendingStatus`) REFERENCES `lending_status` (`id`),
  CONSTRAINT `fk_toolId` FOREIGN KEY (`toolId`) REFERENCES `tool` (`id`),
  CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3

 

[Kampfgummibaerlie]

Also, was mir auffällt:
1.: arbeite mit mehreren Tabellen
2.: Group by ID (Primary Key) ist selten klug
3.: Ich habe wegen einem ähnlichem Problem (ewig und 3 Tage her) zu PostgreSQL gewechselt.

Also:
1 Tabelle der Werkzeuge (id, name)
1 Tabelle Kunden (id, name)
1 Tabelle Verleihungen (id, kunde (FK), werkzeug (FK), timestamprange)

und das magische Ding, welches du suchst, gibt es denke ich so nur bei PostgreSQL, ist eine exclusion constraint.
Ich habe eine Datenbank gebaut, wo mehrere Maschinen an Kunden vermietet werden, sollte also ein ähnlicher Fall sein

Habe bisher nie daran gedacht, auf ein anderes SQL als PostgreSQL zu wechseln

EDIT: Spaltentyp Range denke ich ist auch PostgreSQL-Spezifisch

 

[myscy]

Ein hoch auf die Suchvorschlagfunktion im Thread-Titel...nächstes Mal sollte ich das Titelfeld als erstes ausfüllen

Dieser Vorschlag aus dem hiesigen Forum führte zur Lösung:
Group by Problem : Spalten zu Zeilen möglich ??

Umgemünzt auf mein Szenario:

WITH tmp AS (
    SELECT toolId, max(lendingDate) lendingDate
    FROM lending
    GROUP BY toolId)
SELECT id, toolId, userId, lendingDate, dueDate, returnedDate, lendingStatus, timestamp
FROM lending
INNER JOIN tmp USING (toolId, lendingDate);

@Kampfgummibaerlie
1.: das tue ich: tool, user, lending
2.: meinst du "GROUP BY toolId"? toolId ist in der Tabelle lending FK. Warum sollte es hier zu Problemen kommen?
3.: Danke für den Tipp. Range als Datentyp finde ich klasse. Leider bin ich aktuell an MySQL/MariaDB gebunden und versuche es mit 2 Feldern (Startdatum und Fälligkeitsdatum) zu lösen

 

[Kampfgummibaerlie]

ich dachte nur, weil in deinem Kommentar alles unter einem create table befehl steht
hätte aus deinem befehl gelesen, dass du nach dem Primary Key gruppieren möchtest.
auch die vielen "default null" iritieren mich ein wenig

 

[myscy]

Stimmt, ich hätte erwähnen sollen, dass es noch 3 weitere Tabellen (tool, user, lending_status) gibt, worauf über die 3 FKs referenziert wird.

Da hast du absolut Recht, die vielen DEFAULT NULLs werde ich (bis auf bei "returnedDate") rausnehmen. Danke für den Tip
Der Timestamp-Wert bei "returnedDate" wird erst bei der Rückgabe gesetzt.

 

[dabadepdu]

Mit "GROUP BY toolID" bekomme ich zwar eine komplette Liste mit alle Werkzeugen hin, aber die Werte in den Spalten (bis auf toolId) gehören nicht zur Zeile mit dem höchsten "lendingDate"-Wert

Du hast es ja geschafft. Ein Group By musst Du immer sehr sorgfältig aufbauen, es kann in Deiner DB sonst Daten anzeigen, die falsch sind.