Verzweifel an schwerem SQL Query [PHP]

[sh1ny]

Hey Leute.

Ich bin's mal wieder mit einem Problem .

Dieses Query:

SELECT re.id, re.rezept_name, round( avg( k.bewertung ) , 1 ) AS bewertung,
re.endprodukt, re.rezept_zubereitung, u.user_nickname 
FROM tbl_rezepte AS re 
INNER JOIN tbl_user AS u ON re.user_fk = u.id 
LEFT JOIN tbl_kommentare AS k ON k.rezept_fk = re.id
ORDER BY re.id DESC 

Bereitet mir seit ein paar Tagen grosse Schwierigkeiten. Es will einfach nicht Funktionieren.

Es sollte mir alle Daten aus tbl_rezepte anzeigen.
Wenn die bewertung von tbl_kommentare zum einzelnen Rezept vorhanden ist,
soll sie mit dem gerundeten durchschnitt der Bewertung 
des einzelnen Rezeptes angezeigt werden.

Ziel:

Pepperoni Gemüse Rezept
Bewertung: 4.5
von 111
Endprodukt: Pepperoni Gemüse

Schneide 3 Pepperonis in kleine Streifen.

SQL Query der Tabellen um die es geht:

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tbl_kommentare`
--

CREATE TABLE IF NOT EXISTS `tbl_kommentare` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bewertung` int(255) DEFAULT NULL,
  `kommentar` text NOT NULL,
  `datum_erstellt` datetime NOT NULL,
  `user_fk` bigint(20) unsigned NOT NULL,
  `rezept_fk` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_fk` (`user_fk`),
  KEY `rezept_fk` (`rezept_fk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tbl_rezepte`
--

CREATE TABLE IF NOT EXISTS `tbl_rezepte` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_fk` bigint(20) unsigned NOT NULL,
  `datum_erstellt` datetime DEFAULT NULL,
  `rezept_name` varchar(255) NOT NULL,
  `rezept_zubereitung` text NOT NULL,
  `endprodukt` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_fk` (`user_fk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `tbl_user`
--

CREATE TABLE IF NOT EXISTS `tbl_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `user_vorname` varchar(255) DEFAULT NULL,
  `user_nickname` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `isAdmin` tinyint(1) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ; 

Ich hoffe ihr könnt mir helfen, Vielen Dank
euer sh1ny

[Kugelfisch23]

Nutzt du MySQL als DBMS? Wenn ja: Die Fehlermeldung

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

bei der Ausführung deines Querys weist explizit auf die Problemursache hin: Es fehlt ein GROUP-BY-Statement. Ausserdem wäre eventuell eine Veknüpfung mit den Benutzerdaten in Form eines LEFT-(OUTER-)JOINs sinnvoller, da u.U. auch Rezepte ohne zugeordneten Autor denkbar wären, z.B. wenn dieser sein Benutzerkonto gelöscht hat.

[sh1ny]

Nach der "GROUP-BY" -Lösung, sollte es so eig. gehen:

SELECT re.id, re.rezept_name, round( avg( k.bewertung ) , 1 ) AS bewertung, re.endprodukt, re.rezept_zubereitung, u.user_nickname
FROM tbl_rezepte AS re
INNER JOIN tbl_user AS u ON re.user_fk = u.id
LEFT JOIN tbl_kommentare AS k ON k.rezept_fk = re.id
GROUP BY re.id
ORDER BY re.id DESC
LIMIT 0 , 30

geht aber leider nicht, denn es gibt mir bei der "k.bewertung" einfach NULL zurück.

[Exterminans]

Wenn da NULL raus kommt, dann bedeutet dass, das der LEFT JOIN keine Datensätze gefunden hat, es also noch keine Kommentare zu dem gewünschten Datensatz gibt. Die Anzahl der Kommentare bekommst du dann übrigens mit COUNT(k.bewertung). Solltest du immer einen numerischen Wert anstelle von NULL haben wollen, so umschließe den Ausdruck der NULL ergeben kann mit der Funktion COALESCE(). COALESCE() gibt dabei den ersten von NULL verschiedenen Parameter zurück, COALESCE(NULL, 1) ergibt also z.B. 1.

[sh1ny]

Also Leute:

Ich habe es nun mit 2 Querys hingebracht.

Es hat sich dann aber ein 2tes problem eingeschlichen, und zwar das Paging.
Ich müsste eine Zahl haben, die mir genau die Anzahl datensätze anzeigt, die aus dem bestimmten Query entstehen.
Nur leider müsste diese Zahl vor der While schleife (zum ausgeben) entstehen.
gibt es irgendeine Funktion inder ich die Anzahl Datensätze dieses Query's:

SELECT re.id, re.rezept_name, round(avg(k.bewertung),1) AS bewertung,
re.endprodukt, re.rezept_zubereitung, u.user_nickname 
FROM tbl_rezepte AS re 
INNER JOIN tbl_user AS u ON re.user_fk = u.id 
LEFT JOIN tbl_kommentare AS k ON k.rezept_fk = re.id
WHERE k.bewertung != 0 AND k.rezept_fk is not NULL
GROUP BY re.id
ORDER BY bewertung DESC

ausgeben kann?

Wäre sehr schön wenn ihr mir helfen könntet.
sh1ny

[Kugelfisch23]

Die Anzahl der Ergebnis-Zeilen eines beliebigen SQL-Querys liefert dir mysql_num_rows().

Bedenke jedoch, dass es weit effizienter wäre, bereits im Query durch eine passende LIMIT-Direktive die Anzahl der zu selektierenden Datensätze zu beschränken, statt alle Datensätze zu selektieren, auszulesen und dann nur einige wenige auszugeben. Um die Anzahl der Datensätze in der Tabelle tbl_rezepte zu bestimmen - diese bestimmt die Anzahl der Seiten, sofern sichergestellt ist, dass zu jedem Rezept genau ein Benutzer existiert oder du anstelle des INNER JOIN ein LEFT JOIN nutzen würdest - könntest du ein sehr viel einfacheres Query wie

SELECT COUNT(*) FROM tbl_rezepte

auslesen.

[sh1ny]

Hmm.. ok danke.

Ich habe nun die MySQL "Funktion" "Union" gefunden. mit dieser Funktion wäre mein vorhaben erheblich einfacher, allerdings weiss ich nicht ob sie bei 2 so grossen Querys funktionieren:

Kann ich das so verwenden?

(
SELECT re.id, re.rezept_name, round(avg(k.bewertung),1) AS bewertung,
re.endprodukt, re.rezept_zubereitung, u.user_nickname, bild 
FROM tbl_rezepte AS re 
INNER JOIN tbl_user AS u ON re.user_fk = u.id 
LEFT JOIN tbl_kommentare AS k ON k.rezept_fk = re.id
WHERE k.bewertung != 0 AND k.rezept_fk is not NULL
GROUP BY re.id
ORDER BY bewertung DESC
)
UNION
(
SELECT re.id, re.rezept_name, round(avg(k.bewertung),1) AS bewertung,
re.endprodukt, re.rezept_zubereitung, u.user_nickname 
FROM tbl_rezepte AS re 
INNER JOIN tbl_user AS u ON re.user_fk = u.id 
LEFT JOIN tbl_kommentare AS k ON k.rezept_fk = re.id
GROUP BY re.id
ORDER BY re.id DESC
LIMIT 0, 10
)

Vielen Dank
sh1ny

[Kugelfisch23]

Bedenke, dass die über UNION vereinigten Zeilen dieselbe Anzahl an Spalten haben und die Spalten von identischen Typen sein müssen (vgl. http://dev.mysql.com/doc/refman/5.1/de/union.html). Das ist in deinem Fall nicht gegeben. Ausserdem stellt sich mir die Frage, was genau du erreichen möchtest - dein Query scheint mir unnötig komplex.