[SQL]Count vanuit join én een laatste waarde

[mithras]

Ik zit met een SQL probleem waar ik niet echt uitkom. Het zal vast heel erg simpel zijn, maar ik kan de oplossing eigenlijk niet verzinnen

Ik heb een aantal items waar je reacties kan geven. Ik wil in een overzicht de items laten zien, met daarbij horend het aantal reacties en de timestamp van de laatst toegevoegde reactie. Dus ik bedacht dit:

SQL:

SELECT I.*, 
  COUNT(R.id) as reactions, 
  R.date
FROM items AS I
LEFT JOIN reactions AS R
  ON I.id=R.report_id
GROUP BY I.id
ORDER BY I.id

Maar date is het eerste bericht (de oudste dus), en niet de laatste (en meest recente). Hoe kan ik nu van het laatste bericht de datum laten zien? Velden vanuit de `reactions` opnemen in de order of groepering voeren niets uit, evenals een MAX op reacties. Ik denk dat laatste komt door de COUNT, en een COUNT() en MAX() niet bij elkaar kunnen. Maar klopt die gedachtegang?

Uiteraard is mijn dank groot

[whoami]

Angezien ik zie dat je group by 'id' doet, ben ik vrijwel zeker dat je MySQL gebruikt.
Als groepeert, moet je op ieder veld groeperen dat in je select lijst staat, en geen aggregate is.
Zie ook: klik

Je zal moeten werken met een gecorreleerde subquery moeten werken.

code:

select items.naam, ( select count(1) from reactions 
                                     where reactions.report_id = items.id ),
                             (select max(reactions.date) from reactions
                                    where reactions.report_id = items.id )
from items

Je zou je model ook kunnen deserializeren, zodanig dat je per item gewoon al een tellertje bijhoud hoeveel reacties er zijn. Dat tellertje verhoog je dan iedere keer een reactie bijkomt voor dat item.

[mithras]

whoami schreef op donderdag 29 november 2007 @ 21:40:
Angezien ik zie dat je group by 'id' doet, ben ik vrijwel zeker dat je MySQL gebruikt.
Als groepeert, moet je op ieder veld groeperen dat in je select lijst staat, en geen aggregate is.
Zie ook: klik

Ik dacht te begrijpen wat een GROUP BY deed. Na de FAQ gelezen te hebben, zie ik ook in waarom je concludeert dat ik MySQL gebruik

Het komt kortweg neer op zo'n tabel:

code:

I id | I name | I project | R id | R name | R message | R date
1    | bla    | project1  | 1    | piet   | boe!      | 2007-11-16
1    | bla    | project1  | 2    | jan    | bla!      | 2007-11-18
2    | bla    | project2  | 3    | piet   | bou       | 2007-11-20

Als ik nu een count doe op `R id` en een group by doe op `I id`, dan moet ik dus alles wat ik uit R selecteer ook nog groeperen. Maar dan krijg ik losse resultaten van R, en niet meer per I. Dus vandaar deze onderstaande oplossingen?

Je zal moeten werken met een gecorreleerde subquery moeten werken.

code:

select items.naam, ( select count(1) from reactions 
                                     where reactions.report_id = items.id ),
                             (select max(reactions.date) from reactions
                                    where reactions.report_id = items.id )
from items

My god, daar ga ik heel erg lang over doen om het te begrijpen Misschien kom ik hier over een aantal minuten uren wel op terug.

Je zou je model ook kunnen deserializeren, zodanig dat je per item gewoon al een tellertje bijhoud hoeveel reacties er zijn. Dat tellertje verhoog je dan iedere keer een reactie bijkomt voor dat item.

Hoe fout-gevoelig is zo'n ontwerp? Ik weet dat React het ook doet, maar er gingen ook wel eens wat tellertjes stuk
En het is natuurlijk niet een tot op het bot genormaliseerde database, dus wat dat betreft niet helemaal schoon...
In hoeverre is de laatste optie een reële optie?

[whoami]

Dergelijke de-normalisatie is voor grote DB's gebruikelijk hoor; zeker bij grote db's haal je daar echt wel performance-winst.
En foutgevoelig: als je een RDMBS hebt die triggers ondersteunt, dan is het redelijk makkelijk en betrouwbaar om met zo'n tellertje te gaan werken.

Als de tellertjes stuk gaan, dan kan je ze makkelijk terug de juiste waarde geven.

Tot op het bot genormaliseerde systemen zijn mooi, maar in sommige gevallen praktisch niet werkbaar. (Nu, dat wil niet zeggen dat je je systeem niet moet normaliseren natuurlijk. Meestal gaat men z'n DB gaan normaliseren, en als de performance tegenvalt, gaat men kijken waar men kan denormaliseren).

[Verwijderd]

mithras schreef op donderdag 29 november 2007 @ 21:56:
My god, daar ga ik heel erg lang over doen om het te begrijpen Misschien kom ik hier over een aantal minuten uren wel op terug.

whoami's oplossing gaat wel werken, maar wanneer je in de query in je startpost 'R.date' vervangt door 'MAX(R.date) AS date' bereik je hetzelfde.
Neemt niet weg dat je wel moet begrijpen wat je wel en niet kunt doen in GROUP BY queries. MySQL is een heel erg beroerde leermeester, omdat 'ie standaard teveel toelaat, en het resultaat dan vaak meer 'God zegen de greep' is...