Wyciągnie danych na podstawie dwóch różnych tabel Opcje

[proton]

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

[kefirek]

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 

[proton]

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

[WiruSSS]

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.

Ten post edytował WiruSSS 22.02.2009, 15:52:23

[proton]

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.

[WiruSSS]

..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 (IMG:http://forum.php.pl/style_emoticons/default/tongue.gif)

Ten post edytował WiruSSS 22.02.2009, 17:12:25

[proton]

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 

[WiruSSS]

..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.

Ten post edytował WiruSSS 22.02.2009, 18:59:53

[kefirek]

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ą (IMG:http://forum.php.pl/style_emoticons/default/biggrin.gif)
Co do zapytania sprawdziłem i działa na 100%

Ten post edytował kefirek 22.02.2009, 19:12:18

[proton]

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.

Ten post edytował proton 22.02.2009, 19:13:15

[WiruSSS]

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 ?

[kefirek]

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 

Ten post edytował kefirek 22.02.2009, 19:26:56

[proton]

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.

[WiruSSS]

...WHERE jest w pierwszej kolejności ...co dozapytania ...jest napewno dobrze ..bo sprawdzałem jes na analogicznej sytuacji (IMG:http://forum.php.pl/style_emoticons/default/tongue.gif) ..jednak jest ono mało wydajne przy dużej ilości rekordów w tebeli którą wybieramy za pomocą FROM