Hi. I need some help to map a SQL query for a 'greatest-n-per-group' problem to exposed DSL. It uses...

# exposed

[jfburdet]

Hi.
I need some help to map a SQL query for a 'greatest-n-per-group' problem to exposed DSL. It uses several JOIN with some subselect, and I have no idea where to start to translate this to the DSL.

Here is the raw query. Any hints will be appreciated 🙂

SELECT p.id, p.title
FROM Publication p
JOIN ( 
	 SELECT psc.publicationId, psc.publicationStateId ,max(psc.id) 
	 FROM PublicationStateChange  psc
	 GROUP BY psc.publicationId
	) lastpsc ON p.id = lastpsc.publicationId and lastpsc.publicationStateId = ps.id
JOIN PublicationState ps ON ps.id = lastpsc.publicationStateId

WHERE ps.tag = "new"
;

[tapac]

I’m not sure what I understand your query well, but something like this should work.

Untitled

[tapac]

You could find test samples here -> https://github.com/JetBrains/Exposed/blob/master/exposed-tests/src/test/kotlin/org/jetbrains/exposed/sql/tests/shared/AliasesTests.kt#L52

[jfburdet]

@tapac
Thanks I read the above link content yesterday for a long time before deciding posting here. I was not able to translate my SQL to DSL reading those examples.
I believe the doc should contains more "cookbook" examples like that.

Thanks for the solution you proposed, i'll test it today.

[jfburdet]

Beside a weird invisible char pasting your code giving me weird compiling error, it mostly work.

Thanks for your time.