MySQL a krutý SELECT cez 4 tabulky

BX · Príspevok od používateľa BX » 04 okt 2013, 17:40

Zdravím!

Potrebujem vybrať dáta zo štyroch tabuliek naraz a nejako sa v tom už strácam. Bojím sa, že aj keď to nejako rozchodím, nebude to v nejakom hlúpom prípade fungovať a teda to nebude 100% správne.
Preto prosím, pomôžte

4 tabulky, orezané o nepodstatné stĺpce
(V podstate jednoduchý systém - Užívateľ založí topic. Ten obsahuje komenty. Topic má hodnotenie od užívateľa, komenty majú zase approve (súhlasím/nesúhlasím))

Kód: Vybrať všetko

CREATE TABLE Users
    ( 
     user_id int(11)  NOT NULL AUTO_INCREMENT, 
     name VARCHAR (128)  NOT NULL , 
     PRIMARY KEY (user_id) ,
    ) 
;

CREATE TABLE Topics
    ( 
     topic_id int(11)  NOT NULL AUTO_INCREMENT, 
     created TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
     ttext TEXT  NOT NULL ,
     user_id int(11)  NOT NULL ,
     PRIMARY KEY (topic_id) ,
     FOREIGN KEY (user_id) REFERENCES Users(user_id) ,
    ) 
;

CREATE TABLE Comments
    ( 
     comment_id int(11)  NOT NULL AUTO_INCREMENT, 
     created TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP, 
     approve BOOLEAN NOT NULL ,
     hidden BOOLEAN  NOT NULL ,
     ctext TEXT ,
     user_id int(11)  NOT NULL ,
     topic_id int(11)  NOT NULL ,     

     PRIMARY KEY (comment_id) ,
     FOREIGN KEY (user_id) REFERENCES Users(user_id) ,
     FOREIGN KEY (topic_id) REFERENCES Topics(topic_id)
    ) 
;

CREATE TABLE Rating
    ( 
     rating_id int(11) NOT NULL AUTO_INCREMENT , 
     value int(11) NOT NULL ,
     user_id int(11) NOT NULL ,
     topic_id int(11) NOT NULL ,
     PRIMARY KEY (rating_id) ,
     FOREIGN KEY (user_id) REFERENCES Users(user_id) ,
     FOREIGN KEY (topic_id) REFERENCES Topics(topic_id)
    ) 
;

A potrebujem z toho:
Topics.topic_id
Topics.created
Topics.ttext
Users.name
Count(Comments.topic_id) - počet neskrytých! komentov k topicu (WHERE Comments.hidden = 0)
Rating.value - pre daný topic WHERE user_id = $premenna, hodnotenie prihláseného užívateľa
Comments.approve- postoj prihláseného užívateľa k Topicu. Takže ak má koment k danému topicu, zobrazí approve. Ak koment nemá, nechá trebárs NULL.

Táák a ako toto, prosím vás, napchať do jediného dotazu?
Spoľahlivo viem ešte vytiahnuť z dvoch tabuliek

Kód: Vybrať všetko

SELECT Topics.topic_id, 
	   Topics.created,	   
	   Topics.ttext,
	   Users.app_name
	FROM Topics JOIN 
        Users ON (Topics.user_id = Users.user_id) 
	WHERE Topics.hidden = 0 AND 
	ORDER BY Topics.created;

Keď sa tam snažím nejak napasovať počet komentov, už sa mi to nedarí - buď hádže zlý výsledok, alebo chybu. Rating som ani neskúšal, lebo vôbec netuším, ako na to.
K tým komentom, skúšal som niečo takéto, ale je to zle. Neviem prečo, ale aj preto sem píšem...

Kód: Vybrať všetko

SELECT Topics.topic_id, 
	   Topics.created,	   
	   Topics.ttext,
	   Users.app_name
	   COUNT(Comments.topic_id) AS commentsCount
	FROM Topics
	JOIN Users ON (Topics.user_id = Users.user_id) 
	JOIN Comments ON (Topics.topic_id = Comments.topic_id) 
	WHERE Topics.hidden = 0 AND 
	      Comments.hidden = 0 AND
	ORDER BY Topics.created;

harrison314

Pisem to sice po dvoch pivach, ale skus

Kód: Vybrať všetko

SELECT Topics.topic_id, 
      Topics.created,      
      Topics.ttext,
      Users.app_name
      COUNT(Comments.hidden = 0) AS commentsCount
   FROM Topics
   JOIN Users ON (Topics.user_id = Users.user_id) 
   JOIN Comments ON (Topics.topic_id = Comments.topic_id) 
   WHERE Topics.hidden = 0 
   GROUP BY Topics.topic_id
   ORDER BY Topics.created;

PS: mas tam nasvie AND, pred order by

BX · Príspevok od používateľa BX » 04 okt 2013, 19:37

Preklepy v syntaxi neriešte, prepisoval som to v zúfalstve, tak je možné, že som tam niečo nechal.
GROUP BY som veru už zabudol, ako sa používa

Díky, vyskúšam.

harrison314

GROUP BY je na zgrupovanie vysledkov, musis ho pouzit, pokial chces pouzivat agregacne funkcie ako COUNT, SUM, AVG,...

Daj vediet ci to ide

BX · Príspevok od používateľa BX » 05 okt 2013, 12:49

Vyzerá, že to ide, len to COUNT(Comments.hidden = 0) nejako nefunguje a počíta to aj skryté komenty. Tak som podmienku presunul do WHERE. Ďakujem

A ešte s tým hodnotením by som rád niečo urobil.

harrison314

Kód: Vybrať všetko

SELECT Topics.topic_id, 
      Topics.created,      
      Topics.ttext,
      Users.app_name,
      COUNT(Comments.comment_id) AS commentsCount,
      AVG(Rating.value) AS ratingValue
   FROM Topics
   JOIN Users ON (Topics.user_id = Users.user_id) 
   JOIN Comments ON (Topics.topic_id = Comments.topic_id) 
   JOIN Rating ON (Topics.topic_id = Rating.topic_id)
   WHERE Topics.hidden = 0 
   GROUP BY    Topics.topic_id        
   ORDER BY Topics.created;

harrison314 napísal:GROUP BY je na zgrupovanie vysledkov, musis ho pouzit, pokial chces pouzivat agregacne funkcie ako COUNT, SUM, AVG,...

Daj vediet ci to ide

select count(id) from tabulka
select avg(cena) from produkty
select sum(body) from hodnotenie where student_id=10
.
.
.

Nabudúce radšej nepíš keď máš napísať blbosť

miso250593

no ja by som povedal že treba myslieť aj na topici bez komentárov, tie ktoré som ako prihlásený user nehodnotil, a tie ktoré som nekomentoval

preto podľa mňa treba s comments tabulkou joinovať 2x, raz na zistenie počtu komentárov, a raz na to či som komentoval
aj keď presne nechápem ako ti funguje to hidden a aprove, ale pokiaľ môžeš komentovať topic len 1x tak by sa to malo dať takto

Kód: Vybrať všetko

SELECT Topics.topic_id
	Topics.created
	Topics.ttext
	Users.name
	Count(DISTINCT c1.comment_id)
	Rating.value /*Hodnotenie uživatela ktorého id = $user, pokiaľ nehodnotil tak null*/
	c2.approve
	
FROM Topics
JOIN Users ON (Topics.user_id = Users.user_id) 
LEFT JOIN Comments c1 ON (c1.topic_id = Topics.topic_id AND c1.hidden = 0)
LEFT JOIN Rating ON (Rating.topic_id = Topics.topic_id AND Rating.user_id = $user)
LEFT JOIN Comments c2 ON (c2.topic_id = Topics.topic_id AND c2.user_id = $user)
GROUP BY Topics.topic_id

a pokiaľ môžem komentovať viac krát, tak treba zistiť čo vlastne chceš selectovať
lebo nejak neviem pochopiť čo vlastne chceš

pokiaľ len to že či prihlásený uživateľ komentoval príspevok, tak by to šlo upraviť cez count, alebo už neviem či mysql podporuje, a ako vlastne funguje to tvoje hidden, a aprove, ale dalo by sa spraviť niečo ako excelovské countif, len proste neviem čo presne potrebuješ, a teda aký je význam tých hidden a aprove (lebo podla mna to je to isté)

BX · Príspevok od používateľa BX » 06 okt 2013, 12:41

miso250593 ďakujem veľmi silno, to už vyzerá nádejne.

Ono je to celé nejaké zložité (ja som to nevymýšľal), tak ešte raz k tomu approve. Approve je len pri komentároch TRUE/FALSE (súhlasím/nesúhlasím s topicom)
Vo výpise topicov ale potrebujem vypísať postoj prihláseného uživateľa, takže ak tento topic komentoval, zobrazí sa hodnota approve, ktorú tomu komentu priradil. Vedľajšim efektom bude informácia, či vôbec prihlásený užívateľ topic komentoval (Ak nie, approve bude NULL. Ak áno, approve bude nastavený) Jeden užívateľ môže jeden topic komentovať len raz (a tento koment upravovať).
Hidden je klasicky "skrytý" - nastavuje admin, môže tiež znamenať "vyradený". Všetko čo je skryté sa v týchto výpisoch neberie do úvahy (ako topic, tak aj komenty), pretože tento výpis je pre užívateľov.

A čerešnička na torte - radenie výsledkov. Bude možné radiť podľa času (created) a ratingu. Problém je v tom ratingu, pretože topic môže ohodnotiť každý užívateľ. V tomto výpise znamená rating skôr My Rating - rating prihláseného užívateľa. Takže zoradiť topics podľa môjho hodnotenia, ktoré je k dispozícií v tabulke Rating. Len je treba vybrať tie moje a zoradiť to podľa hodnôt (Rating.value) s tým, že neohodnotené (Rating.value = NULL) budú na konci.
A potom ešte bude radenie podľa nejakej kombinácie tohoto všetkého a to už bude úplne na hovno

Takže tento posledný dotez od miso250593 vyzerá sľubne, vyskúšam, ale na pohľad by to malo fungovať presne tak, ako chcem.
A rating vlastne vyberiem podobne ako ten koment, takže to snáď už nejako zbúcham.

miso250593

no tak podľa toho čo píšeš by to mohlo fičať tak ako potrebuješ

čo sa týka toho hodnotenia, tak najjednoduchšie by bolo tam hodiť ešte jeden join a potom zoraďovať nejako podľa avg alebo sum alebo čoho to už je jedno

len keď spájaš takto viacero tabuliek zo vzťahom 1:N tak si treba dávať pozor na to čo vznikne, všimni si toho Count(DISTINCT c1.comment_id)
v tom mojom prípade by to tam nemuselo byť, ale pokiaľ by si tam pripojil tie hodnotenia tak už by to tam musí byť DISTINCT

ropman

ale ked tam prida dalsi join na rating a da na to sum(), tak mu to bude davat zle vysledky ak bude mat topic viac commentov.

druha moznost by bola spravit na rating dalsi column v tabulke topics a vytvorit trigger, ktory ten celkovy rating updatne vzdy ked niekto prida novy rating. potom ani netreba nic joinovat = lepsi performance.

miso250593

no veď o tom som hovoril, že na to treba dávať pozor, samozrejme že sa aj to dá vyriešiť (malo by fungovať (súčet/počet komentátov)/počet hodnotení)
do DB by sa nemali ukladať dáta ktoré sa dajú vypočítať, ale v tomto prípade, ak to potrebuje podľa nich aj zoraďovať, tak určite bude lepšie pridať ďalší stĺpec

MySQL a krutý SELECT cez 4 tabulky

MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky

Re: MySQL a krutý SELECT cez 4 tabulky