Sortowanie po danych uzyskanych po złączeniu i grupowaniu, ciężko ten temat sensownie nazwać... Opcje

[elektrrrus]

Witam,

Problem jest następujący:

próbuje dodać do komponentu remository joomli(seria przed 1.5) sortowanie plików po ocenie zamiast po id. Domyślnie jest możliwe sortowanie po id, nazwi, ilośc pobrań i dacie dodania. Z tym nie ma problemu, bo wszytskie te dane znajdują się w jednej tabli, order by i po sprawie. Natomiast spory problem rodzi się przy próbie sortowania po ocenie. Oceny sa w innej tabeli( jos_downloads_log) niż pliki (jos_downloads_files), w dodatku tabela ta nie zawiera tylko i wyłącze ocen, ale także inne, bliżej nieokreslone dla mnie dane. Oceny mają w tabeli jos_downloads_log i kolumnie type wartość 3, natomiast pole value okeśla wagę głosu, pole fileid id pliku do jakiego głos się odnosi.
Próbowałem to zrobić tak:

[SQL] pobierz, plaintext 
SELECT *
FROM jos_downloads_files LEFT JOIN jos_downloads_log ON jos_downloads_files.id = jos_downloads_log.fileid
WHERE jos_downloads_files.containerid =5 AND published =1
GROUP BY fileid
ORDER BY IF( (jos_downloads_log.type = 3), AVG(jos_downloads_log.value), 0);
[SQL] pobierz, plaintext 

ale nie daje to czekiwanych rezultatów, zwraca tylko wyniki które mają (jos_downloads_log.type = 1.
Nie wiem, czy nie przekombinowałem z tym złączeniem, na początku był zamysł, aby pobierać wartość oceny jako jedno z pól i potem to sortować wg. tego, ale nic z tego.
Wydaje mi się, że zrobienie tego po stronie sql jest po prostu fizycznie niemozłiwe, swoją drogą cała bada danych tego ledwo się trzyma kupy, ale ma to być tylko rozwiązanie tymczasowe.

Czy istnieje jakiś sposób na sortowanie wg. podanego warunku?

Ten post edytował elektrrrus 27.10.2008, 20:31:18

[nevt]

jest to jak najbardziej wykonalne w MySQL i niespecjalnie skomplikowane...

[SQL] pobierz, plaintext 
SELECT `jdf`.*, AVG(`jdl`.`value`) AS `ocena`, `jdl`.`type` FROM `jos_downloads_files` AS `jdf` LEFT JOIN `jos_downloads_log` AS `jdl` ON `jdf`.`id` = `jdl`.`fileid` GROUP BY `fileid` HAVING `type`=3 ORDER BY `ocena`;
[SQL] pobierz, plaintext 

[elektrrrus]

Dziękuje bardzo za pomoc, ale to nie daje oczekiwanych rezultatów.
Podane zapytanie zwraca mi tylko jeden rekord i mam dodatkowo problem przy takim zapytaniu w wybraniu rekródw o published = 1 i containerid = 5.
Próbowałem sam pokombinować z podanym zapytaniem, ale nic sensownego nie uzyskałem.

[nevt]

wnioski są proste... albo oceniony jest tylko jeden plik albo źle opisałeś strukturę danych... na moich tabelach o strukturze jak opisałeś zapytanie działa poprawnie...a korekta na dodatkowy warunek jest banalna, jaki problem?

[SQL] pobierz, plaintext 
SELECT `jdf`.*, AVG(`jdl`.`value`) AS `ocena`, `jdl`.`type` FROM `jos_downloads_files` AS `jdf` LEFT JOIN `jos_downloads_log` AS `jdl` ON `jdf`.`id` = `jdl`.`fileid` GROUP BY `fileid` HAVING `type`=3 AND `jdf`.`containerid` = 5 AND `published` = 1 ORDER BY `ocena`;
[SQL] pobierz, plaintext 

[elektrrrus]

Plików z oceną jest więcej, z tego co pobieżnie naliczyłem ok. 40.

Być może napisałem źle strukturę danych, spróbuje to wyjaśnić jeszcze raz.
Mam dwie tabele:
jos_downloads_files, który ma interesujące pola:
id, container, published

jos_downloads_log, zawierający:
fileid - odniesnie do pliku
type - jeśli ma wartośc 3 jest głosem na plik
vote - zawiera liczby od 1 do 5 będące oceną.

Problem: wyciągnięcie wszytskich danych z tableli jos_downloads_files, posortowanych po średniej z wszytskich ocen jaki ma plik, także pliki bez oceny maja być wyciągnięte.

Ten post edytował elektrrrus 27.10.2008, 22:04:05

[nevt]

natomiast pole value okeśla wagę głosu

a teraz:

vote - zawiera liczby od 1 do 5 będące oceną

wiesz już co trzeba poprawić? jeżeli nie, to pomogę (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[SQL] pobierz, plaintext 
SELECT `jdf`.*, AVG(`jdl`.`vote`) AS `ocena`, `jdl`.`type` FROM `jos_downloads_files` AS `jdf` LEFT JOIN `jos_downloads_log` AS `jdl` ON `jdf`.`id` = `jdl`.`fileid` GROUP BY `fileid` HAVING `type`=3 AND `jdf`.`containerid` = 5 AND `published` = 1 ORDER BY `ocena`;
[SQL] pobierz, plaintext 

[elektrrrus]

arghhhh (IMG:http://forum.php.pl/style_emoticons/default/sciana.gif)
Zgłupiałem już, przepraszam, bo wprowadziłem w błąd niechcący, chociaż czytałem to co pisze dwa razy to i tak napisałem źle.

Nie ma pola vote, jest tylko value.

Może dla jasności sytuacji oto pełny zrzut struktury obu tabel:
jos_downloads_files:

[SQL] pobierz, plaintext 
`id` int(11) NOT NULL AUTO_INCREMENT,
  `sequence` int(11) NOT NULL DEFAULT '0',
  `windowtitle` varchar(255) NOT NULL DEFAULT '',
  `realname` varchar(255) NOT NULL DEFAULT '',
  `islocal` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  `containerid` smallint(6) NOT NULL DEFAULT '0',
  `filepath` varchar(255) NOT NULL DEFAULT '',
  `filesize` varchar(255) NOT NULL DEFAULT '',
  `filetype` varchar(255) NOT NULL DEFAULT '',
  `filetitle` varchar(255) NOT NULL DEFAULT '',
  `description` text NOT NULL,
  `smalldesc` text NOT NULL,
  `autoshort` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  `license` text NOT NULL,
  `licenseagree` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `price` int(11) NOT NULL DEFAULT '0',
  `currency` char(3) NOT NULL DEFAULT '',
  `downloads` int(11) NOT NULL DEFAULT '0',
  `url` varchar(255) NOT NULL DEFAULT '',
  `icon` varchar(50) NOT NULL DEFAULT '',
  `published` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
  `registered` tinyint(3) UNSIGNED NOT NULL DEFAULT '2',
  `userupload` tinyint(3) UNSIGNED NOT NULL DEFAULT '3',
  `recommended` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `recommend_text` text NOT NULL,
  `featured` tinyint(4) NOT NULL DEFAULT '0',
  `featured_st_date` date NOT NULL DEFAULT '2000-01-01',
  `featured_end_date` date NOT NULL DEFAULT '2000-01-01',
  `featured_priority` smallint(6) NOT NULL DEFAULT '0',
  `featured_seq` smallint(6) NOT NULL DEFAULT '0',
  `featured_text` text NOT NULL,
  `opsystem` varchar(50) NOT NULL DEFAULT '',
  `legaltype` varchar(50) NOT NULL DEFAULT '',
  `requirements` text NOT NULL,
  `company` varchar(255) NOT NULL DEFAULT '',
  `releasedate` date NOT NULL DEFAULT '1000-01-01',
  `languages` text NOT NULL,
  `company_URL` varchar(255) NOT NULL DEFAULT '',
  `translator` varchar(255) NOT NULL DEFAULT '',
  `fileversion` varchar(50) NOT NULL DEFAULT '',
  `fileauthor` varchar(100) NOT NULL DEFAULT '',
  `author_URL` varchar(255) NOT NULL DEFAULT '',
  `filedate` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `filehomepage` varchar(255) NOT NULL DEFAULT '',
  `screenurl` varchar(255) NOT NULL DEFAULT '',
  `plaintext` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `isblob` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `groupid` smallint(6) NOT NULL DEFAULT '0',
  `submittedby` mediumint(9) NOT NULL DEFAULT '0',
  `submitdate` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  `lang` varchar(15) NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

jos_downloads_log:

[SQL] pobierz, plaintext 
id int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `date` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
  userid mediumint(9) NOT NULL DEFAULT '0',
  fileid int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  ipaddress char(15) NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

Dziękuje za dotychczasową pomoc i przepraszam za zamieszanie.

Ten post edytował elektrrrus 27.10.2008, 22:39:22

[osiris]

[SQL] pobierz, plaintext 
SELECT files.*, avgs.value
FROM jos_downloads_files AS files 
LEFT JOIN (SELECT file_id , AVG(value) FROM jos_downloads_log WHERE type=3 GROUP BY file_id) AS avgs ON files.id = avgs.file_id
ORDER BY avgs.value
[SQL] pobierz, plaintext 

albo

[SQL] pobierz, plaintext 
SELECT files.*, (SELECT AVG(value) FROM jos_downloads_log WHERE type=3 AND files.id = file_id) AS avg_value
FROM jos_downloads_files AS files 
ORDER BY avg_value
[SQL] pobierz, plaintext 

[elektrrrus]

Drugie z proponowanych przez osiris-a po dodaniu where na containerid zadziałało.
Dziękuje bardzo za pomoc .


Mimo wszytsko w nowej wersji kodu chyba zastanowie się nad dodaniem kolumny z wagą głosu do tabeli z plikami, kolumna z głosem byłaby aktualizowana wyzwalaczem przy kazdym oddaniu głosu. Wydaje mi się, że to będzie wydajniejsze rozwiązanie.