Order by bei JOIN's (Datenbanken und Verzeichnisdienste)

[thread]3806[/thread]

Order by bei JOIN's

Leser: 3

Froschpopo

2007-05-08 21:44

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Folgendes Statement:

Code: (dl )

SELECT a.name, fotos.id FROM users a LEFT JOIN fotos ON fotos.user = a.user WHERE a.id = 'Froschpopo'

Aufgabe: users enthält alle Benutzer. fotos die Fotos der Benutzer.
nun möchte ich das zuletzt hochgeladene Foto von Froschpopo haben.

Code: (dl )

SELECT a.name, fotos.id FROM users a LEFT JOIN fotos ON fotos.user = a.user ORDER BY fotos.datum DESC WHERE a.id = 'Froschpopo'

Angenommen, es gibt mehrere Fotos des Nutzers. Wie kann ich nur das letzte anfordern?

renee

2007-05-08 21:51

User since
2003-08-04
14371 Artikel
ModeratorIn

Wenn id aufsteigend ist:

Code: (dl )

ORDER BY fotos.id DESC LIMIT 1

OTRS-Erweiterungen (http://feature-addons.de/)
Frankfurt Perlmongers (http://frankfurt.pm/)
--

Unterlagen OTRS-Workshop 2012: http://otrs.perl-services.de/workshop.html
Perl-Entwicklung: http://perl-services.de/

Froschpopo

2007-05-08 23:12

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

ah, also garnicht in den JOIN sondern allgemein. DANKE :)

Froschpopo

2007-05-09 01:38

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Also, jetzt gehts weiter hier!

Code: (dl )

SELECT users.name, fotos.id AS aktuellstes_foto FROM users
   LEFT JOIN fotos ON
      fotos.user = users.name
ORDER BY users.datum DESC
LIMIT 3,40

Gesucht wird eine Möglichkeit, von jedem User das aktuellste Foto anzuzeigen.
Der obere Code zeigt eine LISTE, also keinen einzelnen User an.
Jeder User hat mehrere Fotos in der Tabelle "fotos".
Nun möchte ich zu jedem Datensatz das aktuellste Foto in die Suchergebnistabelle übernehmen.

Wolln'wa doch mal schauen wer das kann :D

Das Problem am obigen Code ist: Er gibt die einzelnen User sooft aus, wie er Bilder in "fotos" hat. Wenn also "Froschpopo" 3 Fotos hat, wird er auch 3 mal ausgegeben. Komisch oder?\n\n

Sucher

2007-05-09 02:26

User since
2007-03-26
47 Artikel
BenutzerIn
[default_avatar]

Hallo,

in Postgres würde man das mit GROUP BY users.id (oder wie dein PK heisst) und einer aggregate function machen (hier 'max(id)')

Grüße,

Froschpopo

2007-05-09 02:34

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

hmm, leider alles aussichtslos:

Code: (dl )

SELECT users.name, fotos.id, fotos.name AS aktuellstes_foto FROM users
  LEFT JOIN fotos ON
     fotos.userid = users.name AND fotos.id = max(fotos.id)
ORDER BY users.datum DESC
LIMIT 3,40

das einzige was funktioniert ist das hier:

Code: (dl )

SELECT users.name, fotos.name FROM users
   LEFT JOIN photos ON
      fotos.id = (SELECT MAX(id) FROM fotos WHERE userid = users.id)
ORDER BY users.datum DESC
LIMIT 3,40

Leider dauert das Subselect zusätzlich 0.06 Sekunden, also insgesamt 0.07 sec. Trotz INDEX's auf userid und id
:(
heul

Da ist ja meine Mega-Umkreis&Entfernungsberechnung mit Cos/Sin/Tan-usw. fast um die hälfte schneller!\n\n

MartinR

2007-05-09 09:08

User since
2004-06-17
305 Artikel
BenutzerIn
[default_avatar]

vielleicht i.E. so?

Code: (dl )

1
2
3

SELECT users.name, MAX(fotos.id) AS MaxFotoID, fotos.name AS aktuellstes_foto FROM users
 LEFT JOIN fotos ON
    fotos.userid = users.name

\n\n

Froschpopo

2007-05-09 09:14

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Also soweit ich weiss brauch man dann eine Groupierung und das wäre noch langsamer.

Zu MAX()
In deinem Beispiel ist fotos.name nicht derselbe Datensatz wie der durch MAX(fotos.id) ermittelte. Das sind zwei unterschiedliche Paar Schuhe.
MAX() kann man in der Hinsicht vielleicht mit COUNT() vergleichen.
ich befürchte deshalb, dass ein SubSelect notwendig ist...\n\n

MartinR

2007-05-09 09:24

User since
2004-06-17
305 Artikel
BenutzerIn
[default_avatar]

Ich habe z.B. folgendes SQL im Einsatz und das liefert mir immer den jeweils letzten Status mit dem dazugehörigen Datum. Ob das GroupBy die Sache verlangsamt habe ich aber nicht getestet

Quote
SELECT
SQL_CALC_FOUND_ROWS
FD.id,
DATE_FORMAT(FS1.status_datum, '%d.%m.%Y %H:%i') AS meldung_datum,
FD.meldung_name,
FD.meldung_rufnummer,
FD.meldung_mail,
FD.fehler_rufnummer,
FD.fehler_kundennummer,
MAX(FS1.status) AS MaxStatus,
FS1.status_datum
FROM
qkan_fehlerdaten as FD INNER JOIN
qkan_fehler_status as FS1 ON
FD.id = FS1.fehler_id
GROUP BY
FD.id
ORDER BY
$order_by
LIMIT
$start, $MeldungenProSeite

\n\n

Froschpopo

2007-05-09 09:58

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

sortierst du in $order by FS1 oder FD ?

View all threads created 2007-05-08 21:44.