[mysql] Group by vraag

[Lentje]

Dames en heren,

Ik heb een vraag over een query die ik uit wil voeren op de volgende, versimpelde, tabel:

id	content_id	versie_id	titel	gepubliceerd
1	4	1	Mijn goudvis Tommy	1
2	4	2	Mijn goudvis Tommy	0
3	5	1	Mijn hond Boris	1
4	6	1	Huisdieren	0

De tabel bevat verschillende stukken content met daarnaast ook verschillene versie (herzieningen) daarvan. Ik wil nu op een pagina een lijst laten zien per content_id met daarbij onderandere of het item al is gepubliceerd. Dat betekent dus dat ik bij content_id 4 gepubliceerd op 1 wil hebben. Dus eigenlijk een soort if statement: als 1 van de content_id's een gepubliceerde status heeft -> zet gepuliceerd op 1.

De gewenste output bij dit voorbeeld zou dan ook zijn:

content_id	titel	gepubliceerd
4	Mijn goudvis Tommy	1
5	Mijn hond Boris	1
6	Huisdieren	0

Het is volgens mij een redelijk simpele query, maar ik kom maar niet op de juiste manier. Kan iemand mij even op het goede spoor zetten?

Alvast bedankt!

[P-Storm]

Ik vermoed dat je hier niet uitkomt met een group, maar eerder met een subquery. Voor wat meer informatie erover

[GlowMouse]

Je zoekt DISTINCT icm een LEFT JOIN.

[Lentje]

GlowMouse schreef op woensdag 26 oktober 2011 @ 19:48:
Je zoekt DISTINCT icm een LEFT JOIN.

Dat zal niet lukken denk ik aangezien gepubliceerd verschilt.

@P-Storm: ik had al zon vermoeden, maar kreeg het niet voor elkaar.

[ Voor 14% gewijzigd door Lentje op 26-10-2011 19:56 ]

[GlowMouse]

Lentje schreef op woensdag 26 oktober 2011 @ 19:55:
[...]


Dat zal niet lukken denk ik aangezien gepubliceerd verschilt.

Je kunt joinen met als joinconditie (oa.) dat gepubliceerd=1. Als je niks vindt dan krijg je gepubliceerd=NULL, anders krijg je gepubliceerd=1. Met een IF kun je dat wel omtoveren naar 0/1, als je dat in de database al wilt doen.

[Ventieldopje]

Heb de tabel nagemaakt lokaal in MySQL en krijg met de gegevens die jij hebt gegeven en deze query je verwachte resultaat:

SELECT DISTINCT content_id, titel, gepubliceerd FROM `content` GROUP BY `content_id`;

[hostname]

Je kan natuurlijk gewoon de tabel met alleen content_id en titel genereren met een simpele group by. Vervolgens wil je de maximale waarde van gepubliceerd hebben (die is namelijk 1 zodra er een van de waardes een 1 heeft). Dus je voegt MAX(gepubliceed) toe aan je query en hij zou moeten werken.

Ventieldopje schreef op woensdag 26 oktober 2011 @ 19:57:
Heb de tabel nagemaakt lokaal in MySQL en krijg met de gegevens die jij hebt gegeven en deze query je verwachte resultaat:

SELECT DISTINCT content_id, titel, gepubliceerd FROM `content` GROUP BY `content_id`;

Die query is helemaal niet geldig. titel en gepubliceerd staan namelijk niet in een aggregate functie en niet in de GROUP BY. Dat MySQL hem toch accepteert is iets heel anders.

[ Voor 46% gewijzigd door hostname op 26-10-2011 19:59 ]

[GlowMouse]

Ventieldopje schreef op woensdag 26 oktober 2011 @ 19:57:
Heb de tabel nagemaakt lokaal in MySQL en krijg met de gegevens die jij hebt gegeven en deze query je verwachte resultaat:

SELECT DISTINCT content_id, titel, gepubliceerd FROM `content` GROUP BY `content_id`;

Dat is dan toeval, zie Programming FAQ - SQL

[Ventieldopje]

Dat was inderdaad toeval Eventjes puzzelen!

[Lentje]

hostname schreef op woensdag 26 oktober 2011 @ 19:58:
Je kan natuurlijk gewoon de tabel met alleen content_id en titel genereren met een simpele group by. Vervolgens wil je de maximale waarde van gepubliceerd hebben (die is namelijk 1 zodra er een van de waardes een 1 heeft). Dus je voegt MAX(gepubliceed) toe aan je query en hij zou moeten werken.


[...]

Die query is helemaal niet geldig. titel en gepubliceerd staan namelijk niet in een aggregate functie en niet in de GROUP BY. Dat MySQL hem toch accepteert is iets heel anders.

Met de MAX toevoeging was het euvel inderdaad makkelijk te verhelpen! Bedankt!

[Lentje]

Het werkt toch niet zoals gedacht. Ik heb in het select statement toch nog een verschillende waarde als "id" nodig. Dan geeft in een select zoals "SELECT id, content_id, title, MAX(published)" een willekeurige waarde voor published weer.

Weet iemand hoe dat eventueel op te lossen is (met eventueel een subquery)?

[RobIII]

Hoe werkt dat GROUP BY nu eigenlijk?

Het wordt moeilijker als je meerdere kolommen wil hebben
...
MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

M.a.w: hoe ziet je group by er nu uit (en: heb je er überhaupt één)? Je bent je bewust van 't feit dat in de group by alle velden die niet in aggregate functies gebruikt worden opgenomen moeten worden?

[ Voor 78% gewijzigd door RobIII op 27-10-2011 01:04 ]

[Lentje]

Rob, ik denk ook niet dat ik er met een GROUP BY uit kan komen aangezien de velden die ik nodig heb niet allemaal in de GROUP BY kunnen staan aangezien sommige verschillend zijn.

Nu los ik het op door een subquery in PHP uit te voeren die kijkt of er per content_id een item getagged is met published. Maar ik denk dat het veel efficienter kan. Alhoewel een subquery in Mysql niet per definitie sneller hoeft te zijn toch?

[P.O. Box]

Lentje schreef op donderdag 27 oktober 2011 @ 10:33:
Rob, ik denk ook niet dat ik er met een GROUP BY uit kan komen aangezien de velden die ik nodig heb niet allemaal in de GROUP BY kunnen staan aangezien sommige verschillend zijn.

Nu los ik het op door een subquery in PHP uit te voeren die kijkt of er per content_id een item getagged is met published. Maar ik denk dat het veel efficienter kan. Alhoewel een subquery in Mysql niet per definitie sneller hoeft te zijn toch?

je maakt misbruik van een gat dat MySQL je biedt te misbruiken

ik zou eens kijken naar HAVING i.c.m. MAX... dan moet je er denk ik wel uitkomen met je query

[GlowMouse]

Lentje schreef op donderdag 27 oktober 2011 @ 10:33:
Rob, ik denk ook niet dat ik er met een GROUP BY uit kan komen aangezien de velden die ik nodig heb niet allemaal in de GROUP BY kunnen staan aangezien sommige verschillend zijn.

Wat bedoel je met verschillend?

P.O. Box schreef op donderdag 27 oktober 2011 @ 13:19:
[...]
ik zou eens kijken naar HAVING i.c.m. MAX... dan moet je er denk ik wel uitkomen met je query

Ehm, nee.

[Nick_S]

Misschien is het lastig om je query op te schrijven omdat je datamodel verkeerd is? Zoals ik hem nu lees kunnen er per content item verschillende versies gepubliceerd zijn? Is dit ook daadwerkelijk de insteek of zou er maximaal 1 versie per content item gepubliceerd mogen zijn. Als dat het geval is het misschien beter om bij je content item een id op te nemen van de gepubliceerde versie.

[Thund3r|IA]

Dit is toch gewoon mogelijk met een subquery:

SQL:

SELECT * FROM content as t1
WHERE t1.id = (
    SELECT id 
    FROM content as t2 
    WHERE t1.content_id = t2.content_id
    ORDER BY gepubliceerd DESC, versie_id DESC 
    LIMIT 1
)

Of zie ik nu iets over het hoofd?

[hostname]

Wat is het probleem met deze query?

SQL:

SELECT content_id, titel, MAX(gepubliceerd) AS gepubliceerd
FROM content
GROUP BY content_id, titel

Deze geeft mij gewoon de goede resultaten voor de data uit je TS. Als je tabel uitgebreider is en dat problemen geeft heb je het teveel versimpeld. In dat geval zou het wel helpen als je ook even kan posten tegen welke problemen je nu aanloopt.

[P.O. Box]

GlowMouse schreef op donderdag 27 oktober 2011 @ 13:37:
[...]

Ehm, nee.

goed beargumenteerd... kan best zijn dat wat ik zei hier niet de oplossing is... vind namelijk niet heel duidelijk wat de TS nou écht wil, maar licht het vooral toe...

[GlowMouse]

P.O. Box schreef op donderdag 27 oktober 2011 @ 16:12:
[...]


goed beargumenteerd... kan best zijn dat wat ik zei hier niet de oplossing is... vind namelijk niet heel duidelijk wat de TS nou écht wil, maar licht het vooral toe...

Je komt niet met een oplossing maar met twee steekwoorden. Het is lastig om te laten zien dat er geen query bestaat met die twee steekwoorden die doet wat TS wil.

HAVING en MAX kun je gebruiken om alleen die groepen terug te krijgen die aan een conditie op het maximum voldoen. Ik zie niet hoe je dat hier kunt gebruiken.

[P.O. Box]

GlowMouse schreef op donderdag 27 oktober 2011 @ 16:25:
[...]

Je komt niet met een oplossing maar met twee steekwoorden. Het is lastig om te laten zien dat er geen query bestaat met die twee steekwoorden die doet wat TS wil.

Ik geloof niet dat het de bedoeling is op dit forum een oplossing aan te dragen...

HAVING en MAX kun je gebruiken om alleen die groepen terug te krijgen die aan een conditie op het maximum voldoen. Ik zie niet hoe je dat hier kunt gebruiken.

je kunt de specifieke records terug krijgen die aan het maximum voldoen... nogmaals, mij is niet duidelijk wat de TS nu wil, maar bijv dit zou kunnen:

code:

SELECT content_id, versie_id, titel
FROM tabel
GROUP BY content_id
HAVING gepubliceerd = MAX(gepubliceerd)

[Verwijderd]

P.O. Box schreef op vrijdag 28 oktober 2011 @ 09:21:
[...]

code:

SELECT content_id, versie_id, titel
FROM tabel
GROUP BY content_id
HAVING gepubliceerd = MAX(gepubliceerd)

Ik zie een GROUP BY, maar geen aggregate functie? Je groepeert op content_id, dus dat betekent dat versie_id en titel geaggregeerd worden? Hoe moet ik dat zien?

Ik denk dat je hier toch echt een subquery nodig hebt:

SQL:

SELECT id, content_id, versie_id, titel, gepubliceerd
FROM tabel a
LEFT JOIN
(
  SELECT content_id, gepubliceerd, MAX(versie_id)
  FROM tabel c
  WHERE gepubliceerd =
  (
    SELECT MAX(gepubliceerd)
    FROM tabel d
    WHERE d.content_id = c.content_id
  )
  GROUP BY content_id, gepubliceerd
) b
ON a.content_id = b.content_id AND a.versie_id = b.versie_id 

Maar het is waarschijnlijk beter je tabel te splitsen in twee tabellen: 1 voor de versies (content_id, versie_id, titel, etc.), en 1 voor de "meta-artikel-data" (content_id en gepubliceerde_versie_id) -- aannemende dat er altijd maar 1 versie gepubliceerd kan zijn, natuurlijk.

[ Voor 7% gewijzigd door Verwijderd op 28-10-2011 09:54 . Reden: Query aangepast... ]