Einträge nach Bewertungen in anderer Tabelle sortieren

[Capricorn]

Hallo,

ich habe mir schon etwas in MySQL reingelesen, aber finde hierfür dennoch keine passende Lösung. Vielleicht stehe ich auch einfach nur auf dem Schlauch:

Ich habe folgende 2 Tabellen:

Objekte:
ID | Name | content  | date
 1 | Bsp    | Blablabla | 1273229919
 2 | Bsp3   | Blobloblo | 1262865519
 3 | Bsp2   | Bliblibli | 1268351919

Ratings:
ID | oID | eigenschaft1 | eigenschaft2 | gesamt | recommend
1   | 1   |          4        |       3          |     3.5  |     1       
2   | 1   |          3        |       5          |      4    |     1       
3   | 2   |          2        |       3          |     2.5  |     0

Also es gibt Objekte (die haben einen Namen, einen Inhalt und noch weitere Dinge). Und es gibt Bewertungen die Objekte Bewerten können. Jeweils verschiedene Eigenschaften (von 1 - 5) und am Ende eine Frage, ob man es weiterempfehlen würde (recommend; 1=Ja bzw. 0=Nein). gesamt ist jeweilgs die Durchschnittliche Bewertung.
Die oID in Ratings zeigt zu welchem Objekt die jeweilige Bewertung ist.

Ich möchte jetzt die besten 4 Objekte aus der Objekte Tabelle auslesen, also erstmal mit der besten Gesamtbewertung und dann nach den meisten Weiterempfehlungen sortiert. Objekte die nur einmal bewertet wurden sollten nicht mit einbezogen werden.

Kann man sowas überhaupt anstellen?! Es müsste doch aus Kombinationen mit JOIN/UNION und Group BY gehen, oder?
Danke
Capricorn

[nikosch]

ORDER BY und LIMIT.

[Capricorn]

Ok, wie ich es dann sortiere und auf 4 limitiere verstehe ich, aber wie bekomme ich es hin, dass er mit eine Tabelle ausgibt, die neben dem Namen und der ID des Objekts auch die Menge der Empfehlungen, die Durchschnittsbewertung aller Bewertungen, die Anzahl der Bewertungen und die Anzahl der Empfehlungen enthält? Also am besten so:

id | Name | rates | durchschnitt | recommends
 1 |  Bsp   |   2    |    3.75         | 2

Und das Objekt Bsp2 soll ja nicht mit hineinkommen, da es nur eine Bewertung hat.

SELECT
    ID,
    Name,
    count(Ratings.id) as rates,
    count(Ratings.recommend) as recommends
    ....
FROM Objekte
GROUP BY id
LEFT JOIN Objekte ON Objekte.id= Ratings.oid

So viel hab ich bisher. Würde das so funktionieren?
Und wie begrenze ich das jetzt das es mindestens 2 Bewertungen geben muss und mit der Durchschnittsbewertung aller Bewertungen?

Capricorn

[nikosch]

Mit HAVING?

[Capricorn]

SELECT
    ID,
    Name,
    count(Ratings.id) as rates,
    count(Ratings.recommend) as recommends
    ....
FROM Objekte
GROUP BY id
HAVING count(Ratings.id) >= 2
LEFT JOIN Objekte ON Objekte.id= Ratings.oid

so?! Und das ausrechnen der Durchschnittsbewertung aller Bewertungen?

[Chriz]

Ungetestet:

SELECT o.*
FROM (
  SELECT oID
  FROM Ratings
  GROUP BY oID
  HAVING COUNT(*) > 1
  ORDER BY AVG(gesamt) DESC, SUM(recommend) DESC
) AS x
INNER JOIN Objekte AS o
ON o.ID = x.oID

[Capricorn]

Super! Vielen Dank Chriz. Funktioniert einwandfrei.

Capricorn

[thomas_w]

Noch eine alternative Lösung, wenn Du die berechneten Zwischenwerte aus der ratings Tabelle benötigst. Achtung: Die eine Spalte heist bei mir leider "recommendet"

CREATE TABLE objekte (
 id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 content VARCHAR(50) NOT NULL,
 date INT NOT NULL,
 
 PRIMARY KEY (id)
);

INSERT INTO objekte VALUES 
( 1 , 'Bsp'   , 'Blablabla', 1273229919 ),
( 2 , 'Bsp3'  , 'Blobloblo', 1262865519 ),
( 3 , 'Bsp2'  , 'Bliblibli', 1268351919);

CREATE TABLE ratings (
 id INT NOT NULL,
 oid INT NOT NULL,
 eigenschaft1 INT NOT NULL,
 eigenschaft2 INT NOT NULL,
 gesamt INT NOT NULL,
 recommendet INT NOT NULL,
 
 PRIMARY KEY(id)
);

INSERT INTO ratings VALUES 
(1   , 1   ,          4        ,       3          ,     3.5  ,     1   ),
(2   , 1   ,          3        ,       5          ,      4    ,     1  ),     
(3   , 2   ,          2        ,       3          ,     2.5  ,     0   ),
(4   , 3   ,          4        ,       3          ,     3.5  ,     1   ),
(5   , 3   ,          3        ,       5          ,      4    ,     1  ),     
(6   , 3   ,          2        ,       3          ,     2.5  ,     0   );


SELECT o.id, o.name, o.content, o.date, avg_gesamt, sum_rec
  FROM objekte o
  JOIN (SELECT oid, AVG(gesamt) AS avg_gesamt, SUM(recommendet) AS sum_rec
         FROM ratings
        GROUP BY oid
        HAVING COUNT(*) > 1
       ) r
    ON o.id = r.oid
ORDER BY avg_gesamt DESC, sum_rec DESC;

+----+------+-----------+------------+------------+---------+
| id | name | content   | date       | avg_gesamt | sum_rec |
+----+------+-----------+------------+------------+---------+
|  1 | Bsp  | Blablabla | 1273229919 |     4.0000 |       2 |
|  3 | Bsp2 | Bliblibli | 1268351919 |     3.6667 |       2 |
+----+------+-----------+------------+------------+---------+
2 rows in set (0.00 sec)

mysql>

EDIT
Ich habe gerade gesehen, dass ich bei meinem Beispiel die Spalte "gesamt" als INT anstatt z.B DEC(10,2) definiert habe, deshalb sind die Kommawerte beim INSERT der Testdaten gerundet worden. Ich dachte schon, der AVG kann nicht richtig rechnen, aber ich war's natürlich selber..

Grüße
Thomas

[flobog]

so gehts auch...

SELECT `name` , sum( `gesamt` ) AS 'Gesamt', count( * ) AS 'Anzahl'
FROM `ratings`
INNER JOIN `objekte` ON `objekte`.`id` = `oid`
WHERE `recommendet` =1
GROUP BY `objekte`.`id`
HAVING count( * ) >1
ORDER BY 'Gesamt' DESC , 'Anzahl' DESC