https://kotlinlang.org logo
j

jfburdet

01/13/2020, 2:37 PM
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 🙂
Copy code
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"
;
t

tapac

01/13/2020, 10:41 PM
I’m not sure what I understand your query well, but something like this should work.
j

jfburdet

01/14/2020, 10:13 AM
@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.
Beside a weird invisible char pasting your code giving me weird compiling error, it mostly work. Thanks for your time.
👌 1
5 Views