SQL Statement - Nachrichten

[Marcus-24-D]

Hi, ich hab nen Problem beim erstellen meines Querys.
Folgend versuche ich aus meinen Nachrichten die jeweils letzte Nachricht egal ob gesendet oder empfangen eines users zu ermitteln.
Das klappt zwar, allerdings bekomme ich als Resultat wirklich ALLE Nachrichten.
Ich würde die für eine Übersicht ganz gerne gruppieren.
Und hier kommt das Problem. Beim benutzen von GROUP BY
funktioniert das ORDER BY irgendwie nicht mehr richtig.

Dies Statement liefert mir bislang alle Ergebnisse:

SELECT msg . * , usr.username, usr.timezone, usr.gender, if( msg.senderid = msg.owner, msg.recipientid, msg.senderid ) AS refuid
FROM mailbox AS msg, user AS usr
WHERE msg.owner =XXX
AND usr.id = if( msg.senderid = msg.owner, msg.recipientid, msg.senderid )
#########GROUP BY usr.username#########
order by msg.sendtime desc 

xxx symbolisiert die Session userid des jeweiligen Users,
die mit Raute (#) markierte Zeile hab ich nur zur Übersicht mit dazugepackt,
denn wenn das Group By angewendet wird, bekomme ich nicht das Ergebnis welches ich haben möchte.

Bitte bitte um Anteilname, ich sitz da schon wirkliche 7 Tage dran

Dankeschön

edit: Das ganze hab ich ebend noch mit nem Join getestet. Auch hier das selbe bei der gruppierung.

SELECT msg . * , if( msg.senderid = msg.owner, msg.recipientid, msg.senderid ) AS refuid, usr.gender, usr.username, usr.timezone

FROM mailbox msg

LEFT JOIN user usr ON username = usr.username

WHERE msg.senderid=xxx

AND usr.id = if( msg.senderid = msg.owner, msg.recipientid, msg.senderid )
ORDER BY msg.sendtime DESC 

[eagle275]

das Problem ist exakt dieses group by - denn du hast keinen Einfluß darauf, WELCHER Eintrag zur "Repräsentation" der Gruppe (beim Zusammenfassen wird ja 1 Eintrag für die ganze Gruppe angezeigt ) verwendet wird.

Das kannst du nur mit einer SubQuery erreichen


In deinem Fall ist die Abfrage aber viel einfacher: da du sowieso auf einen einzigen User abstellst, reicht ein simples "LIMIT 1" am Ende der Abfrage - da bekommst du exakt den neuesten Eintrag aufgrund deiner Sortierung - und auch nur EINEN - wenn du 2 ...3 oder mehr neueste Nachrichten haben willst, passt du einfach die Zahl hinterm Limit an

[Marcus-24-D]

ne, das geht ja eben nicht so einfach *g
es ist ja nicht nur ein user. das ist ja das problem.

In der Datenbank sind halt alle Konversationen gespeichert.
jeder bekommt ne userid zugewiesen.. hier sender.id oder owner.id
da sender und owner.id ja öfters auftauchen, muss ich diese gruppieren.
diesem einzelnen user dann muss ich jeweils die aktuelle nachricht zuordnen.
das prinzip ist wie bei facebook.
das doofe ist, wenn ich das mit group by nicht hinbekommen kann,
bin ich gezwungen ein array zu kreieren indem ständige abfragen stattfinden.
das sind bei 30 verschiedenen konversationen rund 60 sql abfragen zur laufzeit.
irgendwie ist mir das ein wenig zu viel, wenn man sich vorstellt das ich viele aktive leute online habe :/
bestes beispiel welches ich vorhabe ist das facebook nachrichtensystem. in der linken übersicht.
es muss doch auch anders machbar sein ohne das ich so viele querys absetzen muss??!!

[wahsaga]

Probier’s mal nach diesem Prinzip: MySQL :: MySQL 5.5 Reference Manual :: 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

[eagle275]

also irgendwie hast du die Hälfte meines Beitrags ignoriert - denn groupwise max läuft bei mysql auf subquery hinaus - aber nach wie vor seh ich in deinen Abfragen ein "WHERE msg.ownerid=XXX" und damit die Beschränkung auf EINEN, und dann reicht Limit

[Marcus-24-D]

mh? irgendwie verstehen wir uns da falsch, ich hab mich falsch ausgedrückt, du oder ich falsch verstanden.
Ich versuchs mal.

Mit 5 leuten habe ich geschrieben.
Mit jedem, sagen wir mal 3 Nachrichten getauscht.

Dann habe ich insg. 15 Nachrichten, je 3 mit jedem von diesen.
Ich möchte aber nicht alle angezeigt bekommen, sondern jeweils die letzte Nachricht mit dem User und dessen Nicknamen von jeweils einem der User.
Also keine doppelten Ausgaben der Nicknames.

Wieso reicht dann da ein simples limit 1 ???

Wenn ich 5 Ergebnisse erwarte?

[Marcus-24-D]

######################
# User 5 - irgendwan..vor 1 woche
######################

######################
# User 2 - trink doch.. vor 9 min
######################

######################
# User 4 - ja, vielleic.. vor 13 tagen
######################


Ich hab das jetzt mal etwas umgestaltet, in der Hoffnung das ich so besser vorwärts komme.
Aber auch das scheitert *g

Ich habe nun 2 Tabellen angelegt.
Die eine, welche die Teilnehmer speichert und die andere wo die Nachrichten Abgelegt werden.

Tabelle nachrichtenbox_conversations hat:
- id
- streamid
- user
- refuser

Tabelle nachrichtenbox hat:
- streamid
- text
- sendtime

Tabelle User hat:
- id
- username

Folgende Abfrage bringt mir das richtige Ergebnis:

SELECT 
conv.user,
conv.refuser,
conv.streamid,
usr.username,
(select text from nachrichtenbox as msg where msg.streamid = conv.streamid order by msg.sendtime desc limit 1) as text
FROM
    nachrichtenbox_conversation as conv
JOIN
    user AS usr
ON
    usr.id = refuser

where user = XXX 

Ein weiteres Problem hier ist, das ich damit recht unflexibel bin.
Ich brauche ja noch sendtime für die Zeitberechnung.
Ein weiteres Subquery für jedes weitere Element ist da doch recht doof.
Mit nem weiteren Join bekomme ich zwar wieder alle Datenfelder,
leider aber auch wieder alle Nachrichten :/
Lässt sich der Join nicht irgendwie limitieren?

Ich hab echt keine Ahnung mehr was ich noch Probieren soll

[eagle275]

irgendwie hast du das Manual zum Thema aber auch nicht richtig gelesen - dein Subquery steckt im Select und gehört aber in den Where - Teil bzw wie hier in ein Join - wie hier im Beispiel lohnt es manchmal einen Self-Join zu bauen, um die gleiche Tabelle noch einmal zur Verfügung zu haben



es sollte sich analog dieses Beispiels bauen lassen

SELECT s1.article, dealer, s1.price
FROM shop s1 
JOIN (   
    SELECT article, MAX(price) AS price   
    FROM shop   GROUP BY article) AS s2   
  ON s1.article = s2.article AND s1.price = s2.price;

hier geht es darum, jeweils den höchsten Preis eines Händlers zu finden, bei dir halt das höchste Datum in Beziehung zu einem Mail-Partner ... sollte sich doch anpassen lassen

[Guenni61]

Hi Marcus-24-D,

die Unterabfrage darf ja nur einen Datensatz/ein Tabellenfeld zurückgeben.

Wenn du mehr Tabellenfelder abfragen willst, kannst du die Tabellenfelder mittels concat_ws(string "Trennzeichen", feld1 [, feld2, feld3 . . .]) zu einem Feld zusammenfassen:

 . . .
(select concat_ws("Trennzeichen", text, sendtime) from nachrichtenbox as msg
 
 where msg.streamid = conv.streamid order by msg.sendtime desc limit 1) as text
. . . 

Im PHP-Code splittest du test wieder auf:

 . . .
list($msg, $gesendet) = explode("Trennzeichen", $abfrage_ergebnis['text']);
 echo 'Nachricht gesendet am: '.$gesendet;
 echo $msg;
. . . 

Gruß
Günni