Pytanie do zapytania Opcje

[quizer]

Witam, mam następujące zapytanie:

[SQL] pobierz, plaintext 
SELECT artykuly.id, artykuly.tytul, artykuly.krotki, artykuly.DATA, artykuly.img, uzytkownicy.nick, kategorie.opis FROM artykuly INNER JOIN uzytkownicy ON artykuly.uzytkownik = uzytkownicy.id INNER JOIN kategorie ON artykuly.typ = kategorie.id INNER JOIN komentarze ON artykuly.id = komentarze.artykul ORDER BY DATA DESC LIMIT 0, 10
[SQL] pobierz, plaintext 

Chciałbym dodać do tabeli wyjściowej jeszcze jedną kolumnę w której będą policzone komentarze dla danego artykułu (count(komentarze.id)). Nie bardzo wiem jak się za to zabrać by dla każdego artykułu był podliczone tylko jego komentarze. Dla pojedyńczego artykułu takie zapytanie wyglądałoby tak:

[SQL] pobierz, plaintext 
SELECT count(komentarze.id), artykuly.id, artykuly.tytul, artykuly.krotki, artykuly.DATA, artykuly.img, uzytkownicy.nick, kategorie.opis FROM artykuly INNER JOIN uzytkownicy ON artykuly.uzytkownik = uzytkownicy.id INNER JOIN kategorie ON artykuly.typ = kategorie.id INNER JOIN komentarze ON artykuly.id = komentarze.artykul WHERE komentarze.artykul = 3 ORDER BY DATA DESC LIMIT 0, 10
[SQL] pobierz, plaintext 

Pozdrawiam

[Mchl]

Kod

SELECT
  artykuly.id, artykuly.tytul, artykuly.krotki, artykuly.DATA, artykuly.img, uzytkownicy.nick, kategorie.opis
FROM
  artykuly
INNER JOIN
  uzytkownicy ON artykuly.uzytkownik = uzytkownicy.id
INNER JOIN
  kategorie ON artykuly.typ = kategorie.id
INNER JOIN (
  SELECT artykul, COUNT(*) FROM komentarze GROUP BY artykul
) AS komentarze ON artykuly.id = komentarze.artykul
ORDER BY DATA DESC
LIMIT 0, 10

Ten post edytował Mchl 26.06.2010, 20:32:04

[quizer]

Dzieki, tego potrzebowałem ;)

[Crozin]

Zliczanie każdorazowo ilości komentarzy to zły pomysł. Rozwiązuje się to poprzez dodatkową kolumnę "ilość komentarzy" w tabeli, której rekordy są komentowane (tutaj: artykuły). Dzięki temu nie musisz katować bazy bazy niepotrzebnym podzapytaniem, które może w znaczący sposób obciążyć bazę.

Inkrementacja/dekrementacja wartości "ilość komentarzy" przy dodawania/usuwaniu komentarza jest jest jakimś problemem.

[thek]

OT: chyba jest ta inkrementacja/dekrementacja problemem... Skrypt na tym forum też tego nie obsługuje Poza tym podpisuję się pod Twoim zdaniem Crozin. Każdorazowe liczenie to ubijanie bazy. Lepiej to wyrzucić jako osobne pole, które odpowiednio in-/dekrementujemy w określonych przypadkach.

[quizer]

Zliczanie każdorazowo ilości komentarzy to zły pomysł. Rozwiązuje się to poprzez dodatkową kolumnę "ilość komentarzy" w tabeli, której rekordy są komentowane (tutaj: artykuły). Dzięki temu nie musisz katować bazy bazy niepotrzebnym podzapytaniem, które może w znaczący sposób obciążyć bazę.

Inkrementacja/dekrementacja wartości "ilość komentarzy" przy dodawania/usuwaniu komentarza jest jest jakimś problemem.

Dzięki za podpowiedź. Postaram się zastosować. Czyli wtedy przy dodawaniu/usuwaniu komentarza trzeba wysłać dwa zapytania do bazy - nowy rekord dla komentarzy i edycja rekordu dla artykułów? I dlaczego inkrementacja/dekrementacja stanowi problem?

[Crozin]

I dlaczego inkrementacja/dekrementacja stanowi problem?

Oczywiście chodziło mi o to, że nie stanowi problemu.

[Mchl]

Dzięki za podpowiedź. Postaram się zastosować. Czyli wtedy przy dodawaniu/usuwaniu komentarza trzeba wysłać dwa zapytania do bazy - nowy rekord dla komentarzy i edycja rekordu dla artykułów? I dlaczego inkrementacja/dekrementacja stanowi problem?

Dwa zapytania, najlepiej w transakcji. Ewentualnie triggery.

[quizer]

Oczywiście chodziło mi o to, że nie stanowi problemu. ;)

Ok, rozumiem ;)

Dwa zapytania, najlepiej w transakcji. Ewentualnie triggery.

Transakcja czyli po prostu:

[PHP] pobierz, plaintext 
$db->autocommit(FALSE);
$db->query("INSERT INTO komentarze [...]");
$db->query("UPDATE artykuly SET ile_komentarzy = ile_komentarzy+1 [...]");
$db->commit();
[PHP] pobierz, plaintext 

Aye?

Ten post edytował quizer 28.06.2010, 16:14:10

[Mchl]

Nie zapomnij o rollbacku

[PHP] pobierz, plaintext 
$db->autocommit(FALSE);
$result = $db->query("INSERT INTO komentarze [...]");
if(!$result || !$db->query("UPDATE artykuly SET ile_komentarzy = ile_komentarzy+1 [...]")) {
  $db->rollback();
} else {
  $db->commit();
}
[PHP] pobierz, plaintext 

Ten post edytował Mchl 28.06.2010, 17:35:48

[quizer]

Dzięki,
mam jeszcze jedno pytanie podobnego sortu. Do wspomnianych artykułów prócz komentarzy chcę dać możliwość oceniania przez użytkowników. I tutaj się pojawia pytanie gdzie te oceny umieszczać by było to najbardziej optymalne. a) w oddzielnej tabeli z ocenami (uzytkownik, artykul, ocena) B) kolumna w tabeli artykuly z ocenami (uzytkownik:ocena;uzytkownik:ocena;), c) w tabeli z użytkownikami (artykul:ocena;artykul:ocena)? Z poprzednich postów wnioskuje też, że nie ma co pobierać AVG z ocen tylko lepiej po prostu dodać kolumnę 'średnia ocen' do tabeli z artykułami?

Ten post edytował quizer 6.07.2010, 12:54:12

[Mchl]

B i C łamie zasady normalizacji. Co nam zostaje?

Czy lepiej liczyć za każdym razem, czy trzymać w dodatkowej kolumnie i odświeżać, czy cachować w tabeli MEMORY albo w inny sposób, to wszystko zależy od tego jak bardzo ta operacja obciąży Ci bazę.

Masz do rozważenia dwa czynniki:
1. Jak często zmieniają się dane do liczenia średniej
2. Jak często potrzebna Ci jest średnia

Jeżeli ta średnia potrzebna Ci jest sporadycznie, to nie ma co sobie bazy zaśmiecać dodatkowymi konstruktami, lepiej ją policzyć.
Jeżeli średnia potrzebna Ci jest często, a dane do liczenia zmieniają się stosunkowo żadko, wygodnie jest zrobić sobie dadatkową kolumnę z wynikiem.
Jeżeli średnia potrzebna jest często a dane do liczenia zmieniają się równie często, warto zastanowić się nad jakimś mechanizmem cache'ującym gdzie średnia będzie odświeżana np raz na minutę.

[Crozin]

Najprawdopodobniej najlepszym rozwiązaniem będzie tutaj hybryda, tj. dodatkowa tabela z głosami (jako podstawa) plus kolumna "ocena" w ocenianym elemencie. Po dodaniu głosu dodajesz nowy rekord do tabeli na głosy (id użytkownika, id ocenianego elementu, data dodania, ip, ocena i co tam jeszcze potrzebujesz) oraz uaktualniasz średnią ocen (czy tam średnią i ilość głosów).

Po co więc dodatkowa tabela, z której się praktycznie nie korzysta? Bo zawiera ona szczegółowe dane przydatne przy generowaniu statystyk, sprawdzaniu czy dany użytkownik już głosował itp. itd.