Relacja z którą sie chyba zaraz poddam... Opcje

[ChanibaL]

Witam, mam taki oto problem z MySQL'em: robię z kolegą strone o filmach (kinomania), strona działa już od dawna, ale chciałem ją przyśpieszyć, zabajerować i przerobić baze danych na MySQL (wcześniej była w 300+ plikach .txt, a co najlepsze jescze wcześniej była cała w... javascript!). Na php to się w miarę znam, ale w MySQL jestem nowy i ten problem mnie chwilowo załamał.

Mam parę tabel (w tej chwili ważne są users, movie, comment, screen) wszystkie połączone przez odpowiednie pola ID (np. komentarze do filmów są połączone movieid itp). Jednak jeśli chcę wyświetlić, przykładowo tak jak w tym kawałku kodu poniżej użytkowników razem z ich statystykami, to nie umiem zbyt dobrze skonstruować zapytania. Narazie doszłem do tego:

[SQL] pobierz, plaintext 
SELECT users.userid, users.nick, users.name, users.www, users.birth, users.city, users.gg, COUNT(comment.commentid) AS comments, AVG(comment.rating) AS avg, SUM(comment.rating) AS sum, SUM(comment.respect) AS respects,
   COUNT(movie.movieid) AS movies, COUNT(screen.screenid) AS screens
 		FROM users, comment, screen, movie
		WHERE users.userid = comment.userid AND users.userid = screen.userid AND users.userid = movie.userid
		GROUP BY users.userid;
[SQL] pobierz, plaintext 

Wszystko jest niby dobrze, ale jeśli jest w bazie użytkownik który nie dodał minimum 1 komentarza , min. 1 filmu i min. 1 screena to go to zapytanie nie zwraca.

Problem tkwi w np. 'users.userid = movie.userid', ale nie wiem jak to inaczej napisać. Przykładowo jeśli użytkownik nie ma na koncie żadnego dodanego filmu to w jaki sposób ma ta relacja zwórić true?

Wiem, że można by te dane wyświetnić za pomocą wielu zapytań SQL, ale chciałem aby pozostało to bardziej zwięzłe.

Mam nadzieje, że nie namieszałem zbytnio jak na swój pierwszy post? W razie problemów mogę dodać tutaj więcej kodu (tworzenie tabel itp), ale nie chciałem zbytnio zaśmiecać forum.

Ten post edytował ChanibaL 3.07.2004, 02:05:55

[orson]

witam ...

[SQL] pobierz, plaintext 
WHERE users.userid = comment.userid AND users.userid = screen.userid AND users.userid = movie.userid AND users.userid != 0
GROUP BY users.userid;
[SQL] pobierz, plaintext 

wydaje mi sie ze powinno podzialac ....
ps. jak cos to zapodaj sql z create tych 4 tabel i ze 2 wiersze ... bedzie mozna potestowac ...

pozdrawiam

[ChanibaL]

Nie, niestety chyba zbytnio zamotałem, nie o to chodziło. users.userid!=0 nie jest tutaj istotne.
Pomogłoby raczej coś w tym stylu:

[SQL] pobierz, plaintext 
SELECT users.userid, users.nick, users.name, users.www, users.birth, users.city, users.gg, COUNT(comment.commentid) AS comments, AVG(comment.rating) AS avg, SUM(comment.rating) AS sum, SUM(comment.respect) AS respects,
   COUNT(movie.movieid) AS movies, COUNT(screen.screenid) AS screens
 		FROM users, comment, screen, movie
		WHERE users.userid = comment.userid AND users.userid = screen.userid OR  #1 AND users.userid = movie.userid OR  #2
		GROUP BY users.userid;
[SQL] pobierz, plaintext 

Gdzie:

• #1 powinno sprawdzać co zwraca coś w stylu:

  [SQL] pobierz, plaintext 
  (SELECT COUNT(*) FROM screen WHERE screen.userid=??aktualny userid??) = 0
  [SQL] pobierz, plaintext 

• a #2 coś w stylu:

  [SQL] pobierz, plaintext 
  (SELECT COUNT(*) FROM movie WHERE movie.userid=??aktualny userid??) = 0
  [SQL] pobierz, plaintext 

Poprostu nie wiem jak to mam napisać żeby bylo dobrze w skladni MySQL.




A teraz duża dawka spamu, czyli kod tworzący tabele:

[SQL] pobierz, plaintext 
CREATE TABLE movie (
 movieid INT UNSIGNED NOT NULL AUTO_INCREMENT,
 userid INT UNSIGNED NOT NULL,
 genre VARCHAR (60),
 imdb INT UNSIGNED,
 title VARCHAR (255) NOT NULL,
 ipstamp CHAR(15) NOT NULL,
 timestamp INT UNSIGNED NOT NULL,
 PRIMARY KEY (movieid)
 );
 
CREATE TABLE users (
 userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
 nick VARCHAR (32) NOT NULL,
 pass VARCHAR (32) NOT NULL, 
 name VARCHAR (64), 
 city VARCHAR (64), 
 mail VARCHAR (64), 
 www VARCHAR (128), 
 birth INT UNSIGNED,
 gg INT UNSIGNED,
 about TEXT,
 ipstamp CHAR(15) NOT NULL,
 timestamp INT UNSIGNED NOT NULL,
 PRIMARY KEY (userid),
 UNIQUE KEY (nick)
 );
 
CREATE TABLE comment (
 commentid INT UNSIGNED NOT NULL AUTO_INCREMENT,
 userid INT UNSIGNED NOT NULL,
 movieid INT UNSIGNED NOT NULL,
 rating INT UNSIGNED NOT NULL,
 respect BOOL DEFAULT NULL,
 comment TEXT NOT NULL,
 ipstamp CHAR(15) NOT NULL,
 timestamp INT UNSIGNED NOT NULL,
 PRIMARY KEY (commentid)
 );
 
CREATE TABLE screen (
 screenid INT UNSIGNED NOT NULL AUTO_INCREMENT,
 movieid INT UNSIGNED NOT NULL,
 userid INT UNSIGNED NOT NULL,
 ipstamp CHAR(15) NOT NULL,
 timestamp INT UNSIGNED NOT NULL,
 PRIMARY KEY (screenid) 
 );
 )
[SQL] pobierz, plaintext 

I krotki zrzut tabeli (bez screenow bo nie zrobilem do tego jescze modulu)

[SQL] pobierz, plaintext 
INSERT INTO `comment` VALUES (1, 1, 1, 66, 0, 'bfsd f\r\nsd f\r\nsd fsd\r\nf sdf sdf', '127.0.0.1', 1088980851);
INSERT INTO `comment` VALUES (2, 1, 2, 88, 1, 'dsfsd fsdf sdf\r\nsd f\r\nsd f\r\nsdf sdfs dfsd f ', '127.0.0.1', 1088980873);
INSERT INTO `comment` VALUES (3, 1, 3, 99, 0, '342134\r\n2314\r\n1234\r\n23\r\n4231\r\n4\r\n2314', '127.0.0.1', 1088980892);
INSERT INTO `comment` VALUES (4, 5, 3, 14, 0, 'sdfgsd gsdfg dfg df', '127.0.0.1', 1088980939);
INSERT INTO `comment` VALUES (5, 5, 1, 66, 0, 'sdfsdfg dfs f', '127.0.0.1', 1088980962);
 
INSERT INTO `movie` VALUES (1, 1, 'filmu pierwszego', 1, 'Film#1', '127.0.0.1', 1088980851);
INSERT INTO `movie` VALUES (2, 1, 'filmu drugiego', 1, 'Film#2', '127.0.0.1', 1088980873);
INSERT INTO `movie` VALUES (3, 1, 'filmu ktoregostam', 1, 'Film#3', '127.0.0.1', 1088980892);
 
INSERT INTO `users` VALUES (1, 'ChanibaL', '207023ccb44feb4d7dadca005ce29a64', 'ChanibaL the Antichrist', NULL, NULL, NULL, NULL, 3536555, 'ble ble ble', '127.0.0.1', 1088980781);
INSERT INTO `users` VALUES (2, 'ktos #1', 'd41d8cd98f00b204e9800998ecf8427e', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '127.0.0.1', 1088980793);
INSERT INTO `users` VALUES (3, 'ktos #2', 'd41d8cd98f00b204e9800998ecf8427e', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '127.0.0.1', 1088980798);
INSERT INTO `users` VALUES (4, 'ktos #3', 'd41d8cd98f00b204e9800998ecf8427e', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '127.0.0.1', 1088980802);
INSERT INTO `users` VALUES (5, 'ktos z komentarzami', 'd41d8cd98f00b204e9800998ecf8427e', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '127.0.0.1', 1088980815);
[SQL] pobierz, plaintext 

Ten post edytował ChanibaL 4.07.2004, 23:46:26

[orson]

witam ...

[SQL] pobierz, plaintext 
SELECT u.userid, u.nick, u.name, u.www, u.birth, u.city, u.gg, (count(c.commentid)/3) AS comments, AVG(c.rating) AS avg, SUM(c.rating) AS sum, sum(c.respect) AS respects, (count(m.movieid)/3) AS movies, (count(s.userid)/3) AS screens FROM users AS u, movie AS m, screen AS s LEFT JOIN comment AS c ON (u.userid = c.userid) GROUP BY u.nick;
[SQL] pobierz, plaintext 

chyba dziala ... ale nie jestem pewien kilku rzeczy ... np. co jest w tabeli screen co ona przechowuje ? aha ... nie mam pojecia dlaczego trzeba dzielic przez 3 ... i nie wiem czy 3 jest stale czy zalezy w jakis sposob od danych .... na tych ktore sa w poscie wyzej trzeba dzielic przez 3 ... wszedzie gdzie jest count ... nie sprawdzlem reszty funkcji (avg i sum) wiec musisz sobie sam looknac ... ale mniej wiecej dziala ...

pozdrawiam

[ChanibaL]

Dziekuje, wreszcie to zaskoczylo, chociaz kod nie jest zbyt elegancki, zwlaszcza to ciagle /3, ale dziala i to jest najwazniejsze.

Tabela screen to sa screeny (zdjecia) z filmow.

EDIT: Niestety, po dokladnym przyjrzeniu sie to jest wciaz nie to co trzeba. screens nie jest dobrze obliczane. Najlepszym wyjsciem bylo by zapytanie to z mojego pierwszego posta, tylko z takim warunkiem, zeby jesli ktos nie mial zadnych screenow (lub komentarzy/filmow itp.) to zeby mu w screens podawalo 0. Teraz to poprostu nie daje nic (stwierdza ze jesli user nie ma screenow, to warunek relacji jest falszywy, i nie daje zadnych danych tego usera).

Pozdrawiam

Ten post edytował ChanibaL 6.07.2004, 02:38:52

[orson]

witam ...

hmm ... wlasnie ... ale nie dobrze dziala tylko wynik z tabeli screen .... wiec moze wystarczy dopracowac

pozdrawiam ...

[LukaszLenart]

Wykonaj wiecj LEFT JOIN zamiast FROM tabela1, tabela2, etc.

LEFT JOIN wykonuje dolaczenie wszystkich pasujacych rekordow z prawej tabeli do lewej, jesli nie ma danych to zwraca wartosc pusta (NULL ?).

Powinno dzialac, MySQL jest optymalizowany pod zlaczenia i sam korzystam ze zlaczen nawet na 6-8 tabelach.

[Indeo]

Choćbyście na głowie stawali to jeśli nie użyjecie sprzężeń jednostronnych to żadnym warunkiem WHERE nie zmusicie połączonych tabel aby brakujące elementy z jednej z tabel w sprzężeniu zastąpić zerami.
Do tego właśnie służy konstrukcja LEFT JOIN a łączenie tabel na zasadzie:
..... FROM tabela1,tabela2,tabela3 jest niestety metodą przedszkolaków bo chociażby nie pozwala na sprzężenie lewostronne.

[SQL] pobierz, plaintext 
SELECT users.userid, users.nick, users.name, users.www, users.birth, users.city, users.gg, COUNT(comment.commentid) AS comments, AVG(comment.rating) AS avg, SUM(comment.rating) AS sum, SUM(comment.respect) AS respects,
COUNT(movie.movieid) AS movies, COUNT(screen.screenid) AS screens
FROM users LEFT JOIN comment ON users.userid = comment.userid
LEFT JOIN screen ON users.userid = screen.userid
LEFT JOIN movie ON users.userid = movie.userid
 
GROUP BY users.userid;
[SQL] pobierz, plaintext 

Pozatym warto pamiętać że brakujące pola mają wartość NULL - tzn wartość nieznaną i nie wolno w klauzulach WHERE a ni HAVING stosować porównań jak ze zwykłymi wartościami typu WHERE x!=NULL tylko WHERE x IS NOT NULL, bo nie można porównywać czegoś z wartocią nieznaną.

[ChanibaL]

Nie, niestety to tez jescze nie to. Probowalem z taka sama relacja (co do bajta taka sama, tylko GROUP BY bylo o enter'a wyze ). To wciaz robi jakies ciekawostki w wynikach.

Mozliwe ze nie widac dokladnie do czego jest ta relacja, a to moze troche przeszkadzac. Wiec jest to relacja ktora powinna wczytywac wszystkich uzytkownikow z tabeli users, podawac do kazdego dane ktore wpisal, i relacje właściwą, czyli sumę i średnią punktów z wpisów z tabeli comment które mają to samo userid co w tabeli users, w podobny sposób powinno też wczytywać sumę wpisów w tabelach movie i screen które mają wspólny userid. Wynikiem powinno być więc zestawienie ze statystykami poszczególnych użytkowników.



EDIT: wreszcie znalazłem trochę czasu na dokończenie kinomanii, ale wciąż nie mam tej ^$%^ relacji. Chyba poddam się z nią naprawdę.

Ten kod powyżej robi naprawde niezłe ciekawostki - przy ~380 wpisach filmów, ~700 komentarzach i ~1000 screenów jednemu użytkownikowi dał pare milionów screenów (miał może 500), a do tego komp mi sie prawie powiesił (mysqld miało 95% zużycia systemu przez dobrą minutę, może dwie!)

Pomocy, bo sam to chyba szans nie mam. (coś może pomoże obejrzenie poprzedniej wersji kinomanii - www.hubi.oj.pl/kinomania - wiem że serwis jest troche inny, ale będzie mniej więcej wiadomo jak powinien wyglądać)

Ten post edytował ChanibaL 16.07.2004, 16:45:08