zliczanie i sumowanie Opcje

[acztery]

mam kłopot z relacją. Mysql dziwnie sie zachowuje mimo tego, że oczekuje czego innego.

mam takie zapytanie:

[SQL] pobierz, plaintext 
SELECT * , SUM( ifnull( documents.count, 0 ) ) AS CtrDocuments, count( ifnull( documents.Id, 0 ) ) AS CountDocuments, SUM( ifnull( count, 0 ) ) AS SumDocuments, cat.Title AS Title, cat.Id AS Id, cat.UserId AS UserId, cat.sort AS sort
FROM `cat` LEFT JOIN `documents` ON `documents`.`Cat` = `cat`.`Id` 
LEFT JOIN `user` ON `user`.`user_id` = `cat`.`UserId` 
WHERE (
cat.Lang = 'ddd'
) AND (
documents.Lang = 'ddd'
) AND (
cat.Parent = '0'
)
GROUP BY cat.Id
ORDER BY cat.sort DESC LIMIT 0, 30
[SQL] pobierz, plaintext 

pobiera liste kategori i liczy ile w danej jest rekordów i sumuje kolumne count. Na chwile obecna działa tylko wtedy gdy jest jakis element w danej kategori ale jak nie ma zamiast pokaza 0 to go nie wyswetla mimo tego ze mam LEFT. RIGHT i INNER tak samo

Pod zapytania niewchodzą w gre. Kilka z was moze zdziwic ze 2 razy podaje jezyk dla roznych tabel ale inaczej nie działa :/

[mwojcik]

Jak rozumiem tabele wyglądają następująco :

Tabela ‘cat’ :
+ id
+ title
+ userid
+ sort
+ lang
+ parent
Tabela ‘documents’
+ id
+ cat
+ count
+lang
, gdzie ‘cat.id‘ jest kluczem głównym, a ‘documents.cat’ jest kluczem obcym tabeli ‘cat’.

Możesz na początek spróbować zapytania bez tych warunków IFNULL i zobaczyć co zwraca MySQL.

[SQL] pobierz, plaintext 
SELECT cat.title, cat.id , SUM (documents.count) AS sum_documents, COUNT (documents.id) AS count_documents 
FROM cat LEFT OUTER JOIN documents ON cat.id = documents.cat 
WHERE cat.parent = 0 GROUP BY cat.id, cat.title, cat.sort
[SQL] pobierz, plaintext 

Zapytanie powinno ci zwrócić listę kategorii nadrzędnych (‘parent’ = 0), gdzie w kolumnach sum_documents i count_documents będą odpowiednie wartości w przypadku gdy istnieją jakieś dokumenty w odpowiedniej kategorii lub NULLe, gdy takich nie będzie.
Teraz możesz dołożyć warunek IFNULL, żeby sprawdzić czy poprawnie NULLe będą zamieniane na 0.

[SQL] pobierz, plaintext 
SELECT cat.title, cat.id , SUM (IFNULL(documents.count, 0)) AS sum_documents, COUNT (IFNULL(documents.id, 0)) AS count_documents 
FROM cat LEFT OUTER JOIN documents ON cat.id = documents.cat 
WHERE cat.parent = 0 GROUP BY cat.id, cat.title, cat.sort
[SQL] pobierz, plaintext 

Jeżeli to zadziała to możesz dodać resztę warunków, które potrzebujesz.
Niestety nie mam pod ręką MySQL, więc nie mogę sprawdzić osobiście, ale na moje oko powinno działać.


PS. podawaj następnym razem schemat tabel - oszczędzi trochę czasu .

[acztery]

prawie .. ale mimo tego ze jest daje WHERE cat.Lang = 'Polski' np to liczy we wszystkich jezykach a powinno tylko w polskim

[mwojcik]

A może masz w bazie zapisane 'polski' z małej a nie z dużej litery ?
Pokaż klika rekordów z obu tabel to będziemy mogli się zastanowić dlaczego to nie działa.

[acztery]

http://www.cabin.pl/frontend/baza.sql

tam jest kilka rekordów przypisanych do roznych przykladowych jezykow...

Ten post edytował acztery 23.08.2007, 10:46:40

[mwojcik]

Nie wiem, czy to ty czy ktoś napisał strukturę tej bazy, ale jest... zła. Przede wszystkim ktoś nie rozróżnia po co są typy danych i do czego służą.
Przykład pierwszy z brzegu :
Tabela ‘cat’

[SQL] pobierz, plaintext 
`Id` varchar(255) collate utf8_polish_ci NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

Identyfikator zapisany w postaci stringa ? W dodatku 255 znakowego ? Do zapisu klucza głównego używa się integera i tworzy indeks – wtedy baza przeszukuje rekordy o wiele szybciej .
Tak samo jak w tabeli ‘documents’

[SQL] pobierz, plaintext 
`Cat` varchar(255) collate utf8_polish_ci NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

z poprzednich postow wnioskuje ze jest to klucz obcy do tabeli ‘cat’ – też powinien być integerem.
Następna sprawa, chociażby w tabeli ‘cat’

[SQL] pobierz, plaintext 
`Time` varchar(255) collate utf8_polish_ci NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

Po co ci tyle miejsca na date ? Żeby to dobrze działało przede wszystkim trzeba zmienic strukture tej bazy, bo w przeciwnym razie będą się pojawiac nieoczekiwane bledy, trudne do zdiagnozowania.
Co do języka

[SQL] pobierz, plaintext 
`Lang` varchar(255) collate utf8_polish_ci NOT NULL DEFAULT '',
[SQL] pobierz, plaintext 

czy naprawdę potrzebujesz aż 255 znaków, zeby zapisać informacje o języku ? zmniejsz te pole np. do 2 czy 3 znaków i wpisuj tam informacje w postaci np.’pol’, ‘eng’. Wtedy zapytanie zwracające sumę oraz ilość dokumentów w języku polskim dla każdej kategorii głównej w języku polskim mogloby wygladac tak:

[SQL] pobierz, plaintext 
SELECT cat.title, cat.id , SUM (IFNULL(documents.count, 0)) AS sum_documents, COUNT (IFNULL(documents.id, 0)) AS count_documents 
FROM cat LEFT OUTER JOIN documents ON cat.id = documents.cat 
WHERE cat.parent = 0 AND cat.lang = ‘pol’ AND documents.lang = ‘pol’ GROUP BY cat.id, cat.title
[SQL] pobierz, plaintext 

Generalnie – przepisz ta baze z uwzglednieniem zastosowania odpowiednich typow do przetrzymywanych danych.

[acztery]

wiem wiem zmieniam te struktre od zawsze ale jaks czasu nie mam teraz bede musial go sobie wygospodarować.

Co do Id dokumentu musze tak mieć bo np ja tworze id dla dokumentu na podstawie tytułu dla tytułu ala ma kota id = ala_ma_kota jak tam to na jakac cyfre to przy tworzeniu innych dokumentów zacznie robic sie balag i bede rozne id dla roznych wersji jezykowych np.

powinno byc

ala_ma_kota | Polski
ala_ma_kota | Niemiecki

i tak analogicznie jak dam cyfry z czego auto_incordet odpada bylo by tak

34343 | Polski
34323 | Niemiecki

a jak wyswetlam jakis dokument to na podstawie ID i prefixu jezykowego ale tak czy siak musze to zmienic inne rzeczy ktore napisałes ok

zapytanie ktore podales zwraca to samo co moje

ale dzieki jak zmieni struktre itp dam znac i zobaczymy co wtedy da sie wymyslec

Ten post edytował acztery 23.08.2007, 12:55:28

[mwojcik]

To dziwne, ze zapytanie nie zwraca tego co trzeba - a moze w tabeli 'documents' w polu 'cat' wszystkie pola sa powiazane z polem 'id' w tabeli 'cat' (czyli LEFT JOIN dziala tak jak OUTER JOIN bo nie ma zadnych "krotek wiszących" = czyli kazdy wpis w kolumnie 'cat' w tabeli 'documents' ma swoj odpowiednik w kolumnie 'id' w tabeli 'cat') ?
Co do struktury to rozwiązanie wg. mnie z trzymaniem stringa jako klucza, zeby wydobyc jezyk jest nie do konca optymalne.
Mozesz sprobowac przemodelowac tak baze, zeby nazwy kategorii i dokumentow umieszczac w innych tabelach w odpowiednich jezykach i polaczyc je za pomoca klucza obcego.

[acztery]

wlasnie tak robie. jak nie pomoze to zrobie to na skróty tj. jak liczy mi wszystkie rekordy ze wszystkich jezykow to ilosc wszystkich rek/ilosc jezykow da mi ilosc rekordow dla 1 jezyka :/ ale to najgłupsze wyjscie jakie moze byc