[mysql] group by en order by - Softwareontwikkeling

zondag 21 oktober 2007 22:53

Acties:

Topicstarter

Ik heb een tabel 'fotos', ook heb ik een tabel 'reactions'. Nu is het de bedoeling om de laatste 15 reacties op te halen, ik wil eigenlijk dat van iedere reactie van een foto maar een reactie in beeld komt. Dus een group by reatctions.itemid, het itemid is het id uit de table fotos.

Graag zou ik de nieuwste reacties bovenaan willen hebben, dus een order by reactions.post_date desc erin, nu is het geval dat hij dus de group by doet, en daarna de order by. nu krijg ik dus niet de laatste reactie op een foto maar de eerste reactie, hoe moet ik dit oplossen?

De query die ik gebruik staat hieronder.

code:

SELECT reactions.*, fotos.title
FROM reactions
LEFT JOIN fotos ON reactions.itemid = fotos.id
WHERE reactions.catid = 1
  AND reactions.delete_msg = 0
GROUP BY reactions.itemid
ORDER BY reactions.post_date DESC LIMIT 15

[ Voor 1% gewijzigd door een moderator op 21-10-2007 23:24 . Reden: Query leesbaar gemaakt ]

zondag 21 oktober 2007 22:56

Acties:

DataGhost

iPL dev

Andersom sorteren? Ik heb inderdaad de vraag fout begrepen. Wellicht probeer ik het straks nog eens.

Okee, hier dan:
Wat gebeurt er als je

code:

1	GROUP BY reactions.itemid DESC

gebruikt? GROUP BY doet namelijk impliciet ook een ORDER BY op itemid. Aangezien reacties meestal al op tijdsvolgorde staan lijkt me dat dit goed zal gaan.
editzoveel: hoewel dit natuurlijk niet echt uitmaakt voor dezelfde itemids, ik denk niet dat MySQL naar andere kolommen gaat kijken bij het sorteren.

[ Voor 143% gewijzigd door DataGhost op 21-10-2007 23:08 ]

zondag 21 oktober 2007 23:23

Acties:

jeanj

F5 keeps me alive

Een group by garandeerd niet een orderning. Er vanuit gaan dat het wel goed staat is niet echt een 100% garantie. Op order by kan je een asc of desc doen

check http://publib.boulder.ibm...topic/sqlp/rbafygroup.htm
"Grouping the rows does not mean ordering them. Grouping puts each selected row in a group, which SQL then processes to derive characteristics of the group. Ordering the rows puts all the rows in the results table in ascending or descending collating sequence."

Everything is better with Bluetooth

zondag 21 oktober 2007 23:29

Acties:

Bolukan

Wil je maximaal 15 reacties PER foto of maximaal 15 reacties (op 15 of minder foto's). Het laatste doe je met de huidige LIMIT...

zondag 21 oktober 2007 23:41

Acties:

Martine

Topicstarter

Bedankt voor het leesbaar maken van de query, ik zal er de volgende keer om denken. Die limit 15 is gewoon voor de laatste 15 reacties. En omdraaien met ASC of DESC geeft niet, dan worden de resultaten wel omgedraaid alleen niet de group by.

maandag 22 oktober 2007 01:07

Acties:

P.O. Box

HAVING i.c.m. MAX zijn je vriend als ik zo gauw even kijk...

maandag 22 oktober 2007 04:03

Acties:

Martine

Topicstarter

Bedankt voor de tip, dan heeft maxx het ID van de reactie wat ik wil tonen, alleen hoe komt dat dan op het scherm? Met having reactions.id <= maxx werkt niet.

... Of

kan hier een subquery left join achter? Het is tenslotte HAVING where_condition

code:

SELECT
    reactions.*,
    MAX(reactions.id) as maxx,
    fotos.title
FROM
    reactions
LEFT JOIN
    fotos ON reactions.itemid = fotos.id
WHERE
    reactions.catid = 1
    AND
    reactions.delete_msg = 0
GROUP BY
    reactions.itemid HAVING ??
ORDER BY
    reactions.post_date DESC LIMIT 15

maandag 22 oktober 2007 07:44

Acties:

Voutloos

Je wil dus een groupwise maximum, en op die term vind je heel veel voorbeeldqueries. http://jan.kneschke.de/projects/mysql/groupwise-max geeft een aantal verschillende manieren om het probleem te tackelen, incl. argumentatie welke aanpak een beetje leuk is qua performance.

{signature}

maandag 22 oktober 2007 12:00

Acties:

Martine

Topicstarter

dit wordt niks,,.... iig bedankt voor je inzet, maar kom er voor geen mogelijkheid uit, na een dik 30min klooien.

maandag 22 oktober 2007 18:22

Acties:

P.O. Box

uit de brand volgens mij:

SQL:

SELECT reactions.*, fotos.title
FROM reactions
LEFT JOIN fotos ON reactions.itemid = fotos.id
WHERE reactions.catid = 1
  AND reactions.delete_msg = 0
GROUP BY reactions.itemid
HAVING reactions.post_date = MAX(post_date)
ORDER BY reactions.post_date DESC LIMIT 15

(ff snel gedaan, dus pin me er niet op vast)

maandag 22 oktober 2007 20:02

Acties:

prototype

Cheer Bear

Edwardvb schreef op maandag 22 oktober 2007 @ 18:22:
uit de brand volgens mij:

SQL:

SELECT reactions.*, fotos.title
FROM reactions
LEFT JOIN fotos ON reactions.itemid = fotos.id
WHERE reactions.catid = 1
  AND reactions.delete_msg = 0
GROUP BY reactions.itemid
HAVING reactions.post_date = MAX(post_date)
ORDER BY reactions.post_date DESC LIMIT 15

(ff snel gedaan, dus pin me er niet op vast)

Ik snap TS nog niet echt, maar 'k zal er na het eten wel even nog een keer naar kijken ofzo. In ieder geval wilde ik even opmerken dat deze query naar alle waarschijnlijkheid niet gaat werken omdat afaik in de HAVING clausule enkel aggregaten mogen voorkomen (of die kolommen waarop gegroupeerd is). Dit is omdat de HAVING clausule ertoe dient om groepen te elimineren en daarbij werkt het met enkelwaarden (die je krijgt als gevolg van een aggregate function of door een van de de grouped by waarden te gebruiken). Is alweer een tijdje geleden voor me, dus ik zou 't ook mis kunnen hebben.

maandag 22 oktober 2007 20:27

Acties:

Voutloos

Tja, er zijn meerdere reacties met een bepaald itemid, dus wat betreft is de SELECT reactions.* al 1 grote vraag om random data. Tenzij je dus op een andere manier werkt, en dat wordt al duidelijk zat uitgelegd in eerder gegeven link.

Als TS op een specifiek punt nog een vraag heeft wil ik hem best helpen, maar als hij puur 'dit wordt niks' zegt na het geven van een mooie link, houd het op. Geef dan aub aan wat je wel geprobeerd hebt adhv de reacties in dit topic...

{signature}

maandag 22 oktober 2007 20:40

Acties:

P.O. Box

prototype schreef op maandag 22 oktober 2007 @ 20:02:
[...]

Ik snap TS nog niet echt, maar 'k zal er na het eten wel even nog een keer naar kijken ofzo. In ieder geval wilde ik even opmerken dat deze query naar alle waarschijnlijkheid niet gaat werken omdat afaik in de HAVING clausule enkel aggregaten mogen voorkomen (of die kolommen waarop gegroupeerd is). Dit is omdat de HAVING clausule ertoe dient om groepen te elimineren en daarbij werkt het met enkelwaarden (die je krijgt als gevolg van een aggregate function of door een van de de grouped by waarden te gebruiken). Is alweer een tijdje geleden voor me, dus ik zou 't ook mis kunnen hebben.

ach je hebt gelijk ook... kzit niet goed na te denken... volgens mij moet de max wel in de where genoemd kunnen worden... heb dat vaak genoeg gedaan, maar de preciese syntax weet ik zo even niet zeker uit de kop...
maar lijkt me dat TS dat ook prima zelf kan proberen....

maandag 22 oktober 2007 21:11

Acties:

Martine

Topicstarter

Misschien wordt het door de onderstaande tabellen allemaal een beetje duidelijker. Mijn excuses voor de 'vreemde' reactie, maar het zat vanmiddag allemaal niet echt mee.

Poging tot een betere uitleg van het probleem
alle foto's staan in de tabel fotos, het kan voorkomen als er op een bepaalde foto meer dan een reactie komt, de bedoeling is om alleen de laatste / nieuwste reactie in een tabelletje op de beginpagina weer te geven.
Als er geen reactie op de foto is, wordt er ook niets weergegeven. De limit is gewoon, de 15 laatste reacties, alle reacties staan in de tabel reactions.

Het zal best mogelijk zijn om het in meerdere querys te doen, alleen ik ben zo'n gek die zijn code altijd zo klein en kort mogelijk wil houden.

code:

CREATE TABLE fotos (
  id int(11) NOT NULL auto_increment,
  post_date datetime NOT NULL default '0000-00-00 00:00:00',
  title varchar(42) NOT NULL default '',
  active int(11) NOT NULL default '0',
  views int(11) NOT NULL default '0',
  UNIQUE KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE reactions (
  id int(11) NOT NULL auto_increment,
  itemid int(11) NOT NULL default '0',
  catid int(11) NOT NULL default '0',
  userid int(11) NOT NULL default '0',
  content text NOT NULL,
  post_date datetime NOT NULL default '0000-00-00 00:00:00',
  delete_msg varchar(250) NOT NULL default '0',
  delete_date datetime NOT NULL default '0000-00-00 00:00:00',
  delete_by_userid int(11) NOT NULL default '0',
  UNIQUE KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

maandag 22 oktober 2007 21:54

Acties:

prototype

Cheer Bear

Edwardvb schreef op maandag 22 oktober 2007 @ 20:40:
[...]

ach je hebt gelijk ook... kzit niet goed na te denken... volgens mij moet de max wel in de where genoemd kunnen worden... heb dat vaak genoeg gedaan, maar de preciese syntax weet ik zo even niet zeker uit de kop...
maar lijkt me dat TS dat ook prima zelf kan proberen....

Nouja, als je m'n reactie goed leest zie je hoe je dit eigenlijk eenvoudig op kan lossen: namelijk door straffeloos te groeperen ook op reactions.post_date. Ik moet er alleen even bij zeggen dat het formeel gezien handig is om in het geval van een group by expliciet aan te geven waar je op selecteert (ook zodat je kan zien wat wel en niet mag in de group by clausule): volgens mij was MySQL 'een van die db's' die dit toelaat om weg te laten, en impliciet zelf z'n conclusies hierover trekt.

Als dat niet is wat TS wil, zou ik TS even aanraden om naar sql derivatie middels setnotatie te kijken. Dit is een formele methode om SQL queries wiskundig te 'berekenen' middels setcomprehensie. Als ik vanavond nog even tijd heb na 't doen van m'n eigen huiswerk zal ik er wel even naar kijken.

P.S. Ik zou zelf nog even een foreign key constraint overwegen op reactions.itemid (references foto.id). Daar moeten beide tabellen fotos en reactions echter wel innodb voor zijn iirc.

edit:
Even in een paar minuten in elkaar gedraaid. Ook al is correctheid even verkozen boven efficientie, weet ik niet zeker of het klopt. Is alweer een tijdje geleden voor me, en het kan w.s. korter (ik vermoed dat er tautologie is bij de "er is een reactie op een foto", i.e. dat dit gedeelte evt weggelaten kan worden.

De setnotatie icm predicaten.

code:

{ "Laatste reactie van elke foto" }
{ r | "r is een reactie op een foto" & "r is de laatste reactie van elke foto" @ r }
{ r | (EXISTS f @ r.itemid = f.id) & "r is de laatste reactie van elke foto" @ r}
{ r | (EXISTS f @ r.itemid = f.id) & "voor elke f2 is r de meest recente reactie ervan" @ r}
{ r | (EXISTS f @ r.itemid = f.id) & (FORALL f2 | "r op f2" @ "r is de meest recente") @ r}
{ r | (EXISTS f @ r.itemid = f.id) & (FORALL f2 | r.itemid = f2.id @ "er bestaat geen r2 op f2 waarvoor geldt r2.post_date > r.post_date") @ r}
{ r | (EXISTS f @ r.itemid = f.id) & (FORALL f2 | r.itemid = f2.id @ (NOT EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date)) @ r}
{ r | (EXISTS f @ r.itemid = f.id) & (NOT EXISTS f2 | r.itemid = f2.id @ NOT((NOT EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}
{ r | (EXISTS f @ r.itemid = f.id) & (NOT EXISTS f2 | r.itemid = f2.id @ (EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}
{ r, f | r.itemid = f.id & (NOT EXISTS f2 | r.itemid = f2.id @ (EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}

Hieruit volgt:

SQL:

SELECT
    r.* -- hier moet je dus even bepalen wat je precies van r wil.
FROM
    reactions r, fotos f
WHERE
    r.itemid = f.id
AND
    NOT EXISTS(
        SELECT
            *
        FROM
            fotos f2
        WHERE
            r.itemid = f2.id
        AND
            EXISTS(
                SELECT
                    *
                FROM
                    reactions r2
                WHERE
                    r2.itemid = f2.id
                AND 
                    r2.post_date > r.post_date
            )
    )
ORDER BY r.post_date DESC
LIMIT 15

[ Voor 45% gewijzigd door prototype op 23-10-2007 00:17 ]

maandag 22 oktober 2007 23:59

Acties:

Martine

Topicstarter

Wow! Wat een query, maar hij werkt super, bedankt voor het helpen. $_/-\o_$ nu heb ik eindelijk het resultaat wat ik wou hebben, dan wel niet met max en having, het is opgelost, nogmaals bedankt!!!

dinsdag 23 oktober 2007 00:17

Acties:

prototype

Cheer Bear

Martine schreef op maandag 22 oktober 2007 @ 23:59:
Wow! Wat een query, maar hij werkt super, bedankt voor het helpen. $_/-\o_$ nu heb ik eindelijk het resultaat wat ik wou hebben, dan wel niet met max en having, het is opgelost, nogmaals bedankt!!!

Dat is goed om te horen, maar het is mooier natuurlijk als je ook begrijpt hoe de methode werkt. Bestudeer op z'n minst dus de setnotatie en als je iets niet begrijpt, hou ik me uiteraard van harte aanbevolen in dit topic. Goede gelegenheid om even dit soort dingen voor mezelf op te halen ook.

edit:
Zoals eerder gezegd is er mogelijk sprake van tautologie in mijn setnotatie. Het feit dat r namelijk een reactie is volgt al uit het tweede predicaat, i.e. dat r de laatste reactie is van elke foto. Dat laatste impliceert namelijk dat r al een reactie op een foto is. De setnotatie wordt dan:

code:

{ "Laatste reactie van elke foto" }
{ r | "r is de laatste reactie van elke foto" @ r }
{ r | "r is de laatste reactie van elke foto" @ r}
{ r | "voor elke f2 is r de meest recente reactie ervan" @ r}
{ r | (FORALL f2 | "r op f2" @ "r is de meest recente") @ r}
{ r | (FORALL f2 | r.itemid = f2.id @ "er bestaat geen r2 op f2 waarvoor geldt r2.post_date > r.post_date") @ r}
{ r | (FORALL f2 | r.itemid = f2.id @ (NOT EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date)) @ r}
{ r | (NOT EXISTS f2 | r.itemid = f2.id @ NOT((NOT EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}
{ r | (NOT EXISTS f2 | r.itemid = f2.id @ (EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}
{ r |(NOT EXISTS f2 | r.itemid = f2.id @ (EXISTS r2 | r2.itemid = f2.id @ r2.post_date > r.post_date))) @ r}

De query zou dan worden:

SQL:

SELECT
    r.* -- hier moet je dus even bepalen wat je precies van r wil.
FROM
    reactions r
WHERE
    NOT EXISTS(
        SELECT
            *
        FROM
            fotos f2
        WHERE
            r.itemid = f2.id
        AND
            EXISTS(
                SELECT
                    *
                FROM
                    reactions r2
                WHERE
                    r2.itemid = f2.id
                AND 
                    r2.post_date > r.post_date
            )
    )
ORDER BY r.post_date DESC
LIMIT 15

Als het goed is scheelt dit een carthesisch product.

[ Voor 57% gewijzigd door prototype op 23-10-2007 00:43 ]

dinsdag 23 oktober 2007 08:26

Acties:

Voutloos

Die query werkt, maar performance zou wel eens niet ideaal kunnen zijn omdat er 2 dependent subqueries in staan.

Serieus, (3e poging) de link die ik gaf behandelt meerdere opties voor dit probleem en 1 van die opties heb ik in 1 minuut tijd opgeschreven op basis van TS zijn tabellen.

SQL:

SELECT
    r.* -- hier moet je dus even bepalen wat je precies van r wil.
FROM
    reactions r,
    (SELECT MAX(id) as max_id
        FROM reactions
        GROUP BY itemid) as r2
WHERE r.id = r2.maxid
ORDER BY id DESC --of r.post_date, leef je uit
LIMIT 15

Ja, nog steeds een subquery, maar in de explain verschijnt nu gewoon een enkele derived table ipv 2 dependent subqueries. Het wordt dus maar 1x gedaan en deze subquery is helemaal niet duur.

[ Voor 3% gewijzigd door Voutloos op 23-10-2007 10:30 . Reden: order by clause vergeten ]

{signature}