ugrás a tartalomhoz

SQL probléma

MadBence · 2009. Jan. 17. (Szo), 12.56
Sziasztok!

Egy olyan problémám lenne, hogy az egyik SQL kérésem rossz eredményt ad vissza. Egy sima blogmotorhoz van, bejegyzés lekérése történik meg benne tag-ekkel, kommentek számával. Egész konkrétan a kommentek száma rossz, mindig beszorzódik a tag-ek számával.
SELECT 	entry.id, title, entry.author, public, allowComments, entry.date, content, preview,
	GROUP_CONCAT(tag.name ORDER BY tag.name) AS tnames,
	COUNT(comment.id) AS comments
FROM entry 
	LEFT JOIN tag_relationship ON tag_relationship.eid=entry.id
	LEFT JOIN tag ON tag.id=tag_relationship.tid
	LEFT JOIN comment ON comment.id=entry.id
GROUP BY entry.id
Eddig nem nagyon használtam ilyen bonyolult kéréseket, csak nagyon egyszerűeket, ezt a GROUP_CONCAT-ot is itt láttam valahol.
Azt találtam ki, hogy a COUNT()-os részt leosztom valamivel, de az szerintem nagyon nemszép. Biztos van rá megoldás.

Szerkezet:
entry
-id
-title
-author
-public
-allowComments
-date
-preview
-content

comment
-id
-author
-date
-email
-site
-content
-ip

tag
-id
-name

tag_relationship
-eid //entry.id
-tid //tag.id
‹ Listázási probléma.. . SQL Query optimalizálás - alapok ›
 ■
1

sql

gex · 2009. Jan. 17. (Szo), 14.07
a te lekérdezésednél létrejön minden sor amiben különbözik az entry, a tag és a comment, azaz ha megnézed group by előtt, akkor valami ilyesmit fogsz kapni:
entry1 - tag1 - comment1
entry1 - tag1 - comment2
entry1 - tag1 - comment3
entry1 - tag2 - comment1
entry1 - tag2 - comment2
entry1 - tag2 - comment3
mivel ezt után a bejegyzés azonosító alapján csoportosítod, egyértelmű, hogy a count nem azt az eredményt fogja visszaadni amit vársz.

SELECT
    e.id, e.title, e.author, e.public, e.allowComments, e.date, e.content, e.preview,
    trt.tnames,
    c.comments
FROM
    entry e
    LEFT JOIN (
        SELECT
            tr.eid, GROUP_CONCAT(t.name ORDER BY t.name) tnames
        FROM
            tag_relationship tr
            LEFT JOIN tag t ON tr.tid = t.id
        GROUP BY
            tr.eid
    ) trt ON e.id = trt.eid
    LEFT JOIN (
        SELECT
            id eid, COUNT(id) comments
        FROM
            comment
        GROUP BY
            id
    ) c ON e.id = c.eid
ORDER BY
    e,date DESC
először kicsit furcsa volt, hogy a comment.id-ban az entry.id található, ha nem akarsz összezavarodni, akkor minimum annyit tegyél meg, hogy azt is eid-nek hívod.
2

Köszönöm

MadBence · 2009. Jan. 17. (Szo), 14.20
Alaposan benéztem, mert nálam is eid van a kommenteknél, csak 1-2 teszt kommenttel nem tűnt föl, hogy rossz :).
A kód működik amúgy, kösz!
Nem is tudtam, hogy lehet ilyen összetett LEFT JOIN-okat csinálni. Látszik, hogy van még mit tanulnom.
3

Thx

Max Logan · 2009. Jan. 17. (Szo), 15.16
Imába foglaltam volna a neved, ha ezt a megoldást ennél a kérdésnél leírod. Ezer köszönet, így egy SQL query-vel meg tudom oldani azt, ami jelenleg hárommal működik.
4

nem akartam

gex · 2009. Jan. 17. (Szo), 15.36
nem akartam oda is leírni, inkább azon gondolkoztam, hogy MadBencének linkelem be azt a témát, mert egyetértek Gergellyel abban, hogy a tag-eket külön kérném le. egyrészt a group_concat véges adatot tud visszaadni, másrészt szükség lehet a néven kívül url-re, azonosítóra, stb-re, és nem azért használok relációs adatbázist, hogy az eredményét egy különböző karakterekkel elválasztott sztringbe fűzzem össze (pl: group_concat(concat_ws(' ', tag.id, tag.name, tag.url) order by tag.name)-ből lesz 1|Egy|egy, 2|Kettő|ketto, 3|Három|harom).

átmeneti megoldásnak jó lehet, de szerintem rossz berögződéshez vezet.

egyébként mi volt a három lekérdezés? a kommentek számát is külön vetted?
5

Igen ...

Max Logan · 2009. Jan. 17. (Szo), 15.45
... külön megy most a kommentek száma, de akkor azt ezzel a megoldással hozzácsapom az első lekérdezéshez. A tag-ek meg maradnak külön lekérdezésben. Így megspórolok egy queryt (ami mondjuk az utolsó 10 post lekérdezésénél már mindjárt -10 queryt jelent).
7

???

Hodicska Gergely · 2009. Jan. 17. (Szo), 16.28
A tag-ek meg maradnak külön lekérdezésben. Így megspórolok egy queryt (ami mondjuk az utolsó 10 post lekérdezésénél már mindjárt -10 queryt jelent
Ezek szerint cikkenként kérdezed le ezeket az adatokat? Ez igencsak pazarló. Megvan a 10 utolsó cikked ID-ja, akkor ezzel egy queryvel le tudod kérdezni az összeshez mondjuk a tageket, nem kell 10 queryt futtatni.
8

Igen ...

Max Logan · 2009. Jan. 17. (Szo), 17.01
... minden bejegyzésre lefut egy tag-eket lekérő query.

Hogyan lehet ezt egy lekérdezésben megoldni (le kell kérni a tag-et és az escape-elt változatát – name, escaped_name oszlop) group_concat nélkül? Van tags és tag_relationship tábla.

Update:

Egy ilyen query-t ötlöttem ki:

SELECT t2.post_id, t1.name, t1.escaped_name
FROM `tags` AS t1
LEFT JOIN `tag_relationship` AS t2 ON t2.tag_id = t1.id
WHERE t2.post_id IN (1,2,3)
ORDER BY t2.post_id, t1.name
Ez azt csinálja, amit szeretnék. Utána meg PHP-vel szét kell válogatni az ID alapján és csatolni a post-hoz.
9

észrevételek

Hodicska Gergely · 2009. Jan. 17. (Szo), 18.18
Az escaped_name az micsoda, htmlspecialchars? Én ilyesmit max csak nagyon-nagyon indokolt esetben tárolnék le a DB-ben. Már csak azért is, mert mindenféle kimenet esetén más-más escapelésre van szükség (pl. email, sql, URL, HTML, XML stb.). Ha esetleg amiatt csinálod, mert hogy egy taget adott esetben sokszor meg kell jeleníteni egy oldalon, és nem akarod mindig escapelni, akkor itt segíthet a view réteg okosítása, oldja meg ő, hogy az átadott paraméterek csak egyszerelgyenek escapelve. Ha már optimalizálni szeretnél, akkor pl. megérheti a tageket letárolni a cikkek mellett redundánsan.

Nincs értelme itt left joint használni.

Szintén nincs jelentősége, hogy post_id szerint rendezz, ebben a megoldásban is simán végig tudsz menni enélkül is az eredményhalmazon, és bepakolni a tageket a megfelelő cikkhez.

Én ezt használnám:

SELECT
	tr.post_id
	,group_concat(t.name ORDER BY t.name SEPARATOR '|')
FROM
	tag_relationship tr
	JOIN
	tag t
		ON tr.tag_id = t.id
WHERE
	tr.post_id IN (1, 2, 3)
10

Re

Max Logan · 2009. Jan. 17. (Szo), 18.41
Nem, az escaped_name az lesz, ami az url-ben megjelenik.

Például:
Tag: "új szolgáltatás"
URL-ben : "uj-szolgaltatas"

A query, amit írtál csak akkor fut le, ha beírom ezt a where helyett:

GROUP BY tr.post_id
HAVING tr.post_id IN (1,2,3)
11

having nem kell

Hodicska Gergely · 2009. Jan. 17. (Szo), 20.20
A group by tényeg lemaradt (bár a group concat-ből sejthető :)), having viszont fölösleges, hiszem már leszűrted where-ben. Where helyett meg nagyon rossz ötlet, hisz akkor hagyod, hogy a taljes két tábla join-ja, majd a group by megtörténjen, és csak utána szűrnél, lényegesen lassabb és erőforrás igényesebb lenne.

Nem, az escaped_name az lesz, ami az url-ben megjelenik.

Aha, akkor lehet ez: 
GROUP_CONCAT(CONCAT(name, '|', name_escaped) ORDER BY name SEPARATOR '||')
.
12

Remek

Max Logan · 2009. Jan. 17. (Szo), 20.32
Működik a dolog. Már csak azt nem értem, hogy miért a tr tábla van a from-nál, mikor nem abból kellenek az érdemi adatok, hanem a másik táblából?
13

sorrend

Hodicska Gergely · 2009. Jan. 17. (Szo), 23.59
Logikailag nekem ez illett jobban előre: leszűröm a kapcsolótáblából az adott cikkekhez tartozó tageket, majd ezekhez megszerzem a hozzájuk tartozó adatokat. De igazából mindegy, mert a sorrend elméletileg az, hogy először előáll a FROM részben felsorolt táblák megadott összekapcsolásával egy eredményhalmaz, amivel utána dolgozol, így nem nagyon van annak értelme, hogy "érdemi" tábla. Technikailag persze az RDBMS hatékonyabban fog dolgozni, és adott esetben ebből a szempontból is érdemes a kapcsolótáblát előrevenni, mert ez alapján lehet a legtöbb sort kiszűrni (bár valószínűleg egy ilyen egyszerű esetben ennek nincs jelentősége, még MySQL esetén sem).
6

hogy érdemes akkor?

MadBence · 2009. Jan. 17. (Szo), 16.16
Lehet, hogy szét kelle bontani akkor ezt a query-t is két részre? Hosszú távon (több adatnál) gyorsabb, ha 2 query van?