Thread
#exposed
    j

    jfburdet

    2 years ago
    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

    tapac

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

    jfburdet

    2 years ago
    @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.