Witam

Mam mały problem z wyciąganiem danych na podstawie dwóch innych tabel dokładnie chodzi mi o to że musze z tabeli 'uzytkownicy' wyciagnac userow na podstawie ilosci dodanych komentarzy i ocen gdzie komentarze i oceny są w osobnych tabelach. Zarówno w tabeli komentarzy i ocen jest jedno pole które określa kto napisał ten komentarz czy kto dodał ta ocenę. Udaje mi się wyciągać te dane ale tylko na podstawie jednej tabeli (może pokaże przykład):

[SQL] pobierz, plaintext 
SELECT user_name, user_id, count(user_id) AS ilosc_komentarzy FROM komentarze INNER JOIN uzytkownicy ON komentarz_user=user_id GROUP BY user_name ORDER BY ilosc_komentarzy DESC LIMIT 0, 5
[SQL] pobierz, plaintext 

Teraz jak to przerobić aby wyciągnąć jeszcze ilość ocen i posortować wg. ilosc_komentarzy+ilosc_ocen

Cos w ten desen. Powinno dzialac tylko nazwy kolumn poprawn.

[SQL] pobierz, plaintext 
SELECT u.user_name, u.user_id,
(SELECT COUNT(user_id) FROM komentarze c WHERE (c.komentarz_user=u.user_id)) AS komentarze,
(SELECT COUNT(user_id) FROM oceny o WHERE (o.ocena_user=u.user_id)) AS oceny
FROM uzytkownicy u ORDERY BY komentarze, DESC oceny DESC LIMIT 5
[SQL] pobierz, plaintext 

Nie chce ten sposób działać. Najpierw woła że dane musza być pgrupwoane więc daje GROUP BY u.user_name po czym wychodzi: #1242 - Subquery returns more than 1 row

spróbuj tak:

[SQL] pobierz, plaintext 
SELECT u.user_name username, u.user_id idusera, (COUNT(k.user_id)+COUNT(o.user_id)) punkty
FROM uzytkownicy u
LEFT JOIN komentarze k ON k.user_id=u.user_id
LEFT JOIN oceny o ON o.user_id=u.user_id
GROUP BY username
ORDER BY punkty DESC LIMIT 5
[SQL] pobierz, plaintext 

[edit]

jesli natomiast chcesz sortować osobno wg ocen i komentarzy to by wyglądało chyba tak:

[SQL] pobierz, plaintext 
SELECT u.user_name username, u.user_id idusera, COUNT(k.user_id) komentarzy, COUNT(o.user_id) ocen
FROM uzytkownicy u
LEFT JOIN komentarze k ON k.user_id=u.user_id
LEFT JOIN oceny o ON o.user_id=u.user_id
GROUP BY username
ORDER BY komentarzy DESC, ocen DESC LIMIT 5
[SQL] pobierz, plaintext 

..dobrze by było żebyś podał strukturę tabel bo tak to nie wiem jakie nazwy kolumn dać w tych zapytaniach.

Twój przykład też nie chce działać. W przypadku gdy ktoś ma i ocenę i komentarz to w obu wartościach wyświetla to samo.

Nazwy tabel stosujesz dobre, to znaczy jedną rzecz sobie poprawiłem (nie ma user_id w tabelach komentarzy i ocen tylko jest komentarz_name i ocena_name które okreslaja kto napisał/dodał ocenę).

Wychodzi tak jakby nie mogły być dwa LEFT JOIN bo nawet gdy zostaiwe jedno count a nadal będą dwa LEFT JOIN to wyświetla złe wartości dopiero jak zostawie jedno LEFT JOIN i jeden COUNT co zlicza to jest dobrze ale wtedy mam liczbę tylko jednych rzeczy.

..podaj strukturę tabel ..to napewno jest dobrze tylko źle będą nazwy kolumn

..właśnie sobiw przetestowłem takie zapytanie na jednej z moich baz ..wybrałem za jego pomocą userów i posortowałem i ch wg odpowiednio pierw komentarzy dla newsów i potem komentarzy dla artykułów ...zapytanei działa na 100% ..ale niestety nie jest zbyt wydajne przy dużej liczbie rekordów, u mnie jest ponad 150000 rekordów w tabeli z userami i niestety nawet limit nie pomaga gdyż wpierw mysql musi dla każdego usera policzyć wszystkeikomenty potem posortować i na koniec dopiero obcina ..zaraz spróbuje do tego podejśc z innej strony

Struktura komentarzy:

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla  `comments`
--
 
CREATE TABLE `comments` (
  `comment_id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `comment_item_id` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `comment_type` char(2) NOT NULL DEFAULT '',
  `comment_name` varchar(50) NOT NULL DEFAULT '',
  `comment_message` text NOT NULL,
  `comment_datestamp` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `comment_ip` varchar(20) NOT NULL DEFAULT '0.0.0.0',
  PRIMARY KEY  (`comment_id`),
  KEY `comment_datestamp` (`comment_datestamp`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=973 ;
[SQL] pobierz, plaintext 

Gdzie comment_name określa kto napisał komentarz. Oraz struktura ocen:

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla  `ratings`
--
 
CREATE TABLE `ratings` (
  `rating_id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rating_item_id` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `rating_type` char(1) NOT NULL DEFAULT '',
  `rating_user` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
  `rating_vote` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `rating_datestamp` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `rating_ip` varchar(20) NOT NULL DEFAULT '0.0.0.0',
  PRIMARY KEY  (`rating_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2 AUTO_INCREMENT=1594 ;
[SQL] pobierz, plaintext 

Tutaj rating_user określa kto coś ocenił.

No i moje zapytanie wygląda tak:

[SQL] pobierz, plaintext 
SELECT u.user_name username, u.user_id idusera, COUNT( k.comment_name ) komentarzy, COUNT( o.rating_user ) ocen
FROM users u
LEFT JOIN comments k ON k.comment_name = u.user_id
LEFT JOIN ratings o ON o.rating_user = u.user_id
GROUP BY username
ORDER BY komentarzy DESC , ocen DESC LIMIT 5
[SQL] pobierz, plaintext 

..z teg oco widze problemem jest pole comment_name, które powinno zawierać id usera a zawiera chyba jego nick ..sądząc po typie komórki. Więc obecnie rozwiązaniem jest porównanie comment_name z u.username a nei z u.user_id ...jednak zalecałbym stworzyć pole które będzie zawierało id usera i porównywać po tym polu.

Powinno dzialac na 100%

[SQL] pobierz, plaintext 
SELECT u.user_id, user_name, 
(SELECT COUNT(rating_id) FROM ratings r WHERE (r.rating_user = u.user_id)) AS ocen,
(SELECT COUNT(comment_id) FROM comments c WHERE (c.comment_name = u.user_id)) AS komentarzy
FROM users u ORDER BY komentarzy DESC, ocen DESC LIMIT 5
[SQL] pobierz, plaintext 

A tak po za tym to tabele chyba z Fusiona pochodzą
Co do zapytania sprawdziłem i działa na 100%

comment_name zawiera id usera nie jego nick. To że jest typu VARCHAR to jest błąd.

Ok, kefirek działa ten twój sposób.

a takie zagnieżdżanie zapytan nie powoduje jezscze większego spadku wydajności ? ...od wczoraj borykam się z jednym zapytaniem i wklejenie podzapytania powoduje że wydajnośc tego zapytania spada z ~0.05s na ...bagatela ~7.5s ?

Można też tak sprawdzałem i działa

[SQL] pobierz, plaintext 
SELECT u.user_id, user_name , COUNT(rating_id) AS ocen, COUNT(comment_id) AS komentarzy
FROM users u 
LEFT JOIN ratings r ON (u.user_id=r.rating_user)
LEFT JOIN comments c ON (u.user_id=c.comment_name)
GROUP BY u.user_id
ORDER BY komentarzy DESC, ocen DESC LIMIT 5
[SQL] pobierz, plaintext 

To zapytanie działa źle bo sprawdzałem to na małych liczbach i widziałem błąd. Przy pierwszej osobie pobiera znacznie większą liczbę a do tego jeśli ktoś i skomentował i ocenił coś to w obu wartościach (komentarzy i ocen) pokazywało tą samą liczbę.

Sposób który działa też jest nie najwydajnieszy bo widziałem, jednak będe planował aby ranking z tymi wartościami aktualizował się co 24h (co moim zdaniem nie będzie tak obciążało bazy danych - chyba że się mylę).

Do tego mam takie małe pytanie jeśli dodam jeszcze warunek WHERE 'cos=innego' to czy baza najpierw sprawdza rekordy pod względem tego warunku i dopiero potem oblicza te ilości (komentarze i oceny) czy oblicza wartości dla wszystkich i dopiero potem odrzuca te rekordy które nie spełniają warunku. Bo po czasie wykonania widziałem że wykonuje sie szybciej.

...WHERE jest w pierwszej kolejności ...co dozapytania ...jest napewno dobrze ..bo sprawdzałem jes na analogicznej sytuacji ..jednak jest ono mało wydajne przy dużej ilości rekordów w tebeli którą wybieramy za pomocą FROM