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

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

■

Adatbázisok

csirip

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


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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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).

A hozzászóláshoz regisztráció és belépés szükséges
új téma

???

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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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


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.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

é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)


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)

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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)


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

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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 '||')

GROUP_CONCAT(CONCAT(name, '|', name_escaped) ORDER BY name SEPARATOR '||')

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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).

A hozzászóláshoz regisztráció és belépés szükséges
új téma

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?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Weblabor

Keresés

SQL probléma

sql

Köszönöm

Thx

nem akartam

Igen ...

???

Igen ...

észrevételek

Re

having nem kell

Remek

sorrend

hogy érdemes akkor?

Friss blogmarkok

Könyvajánló

Cikkajánló

Friss csiripek

Aktív fórum témák

Új fórum témák

Archívum

Címkék

április 2025
H	K	Sze	Cs	P	Szo	V
31	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	1	2	3	4