Olav Grønås Gjerde

Today I solved something that I had to write about.

I have list of excursions, that can be added to a incident event. So one incident can have multiple excursions. As these excursions are being connected to incidents we only want to display excursions that is not yet connected to a incident.

So how to write this with using Grails Criteria?

List<Excursion> excursions = Excursion.withCriteria {
            
    sensor{
        eq("assetProject", assetProject)
    }
                        
    between("startDateTime", a, b)
    eq("isSuppressed", false)

    if( showOnlyExcursionsNotInIncident ){
                
        // The trick here is the subquery builder
        DetachedCriteria subQuery = DetachedCriteria.forClass(Incident, 'i')
            .with {
                createAlias("i.excursions", "e2")
                setProjection Projections.property('e2.id')
                add Restrictions.conjunction()
                    .add(
                        Restrictions.eqProperty(
                            'e2.id',
                            'this.id'
                        )
                   )
            }
                        
        add Subqueries.notExists(subQuery)
    }
        
    order "startDateTime", "desc"
}

Which will generate the following SQL

select
        ....
    from
        excursion this_ 
    inner join
        sensor sensor_ali1_ 
            on this_.sensor_id=sensor_ali1_.id 
    where
        (
            sensor_ali1_.asset_project_id=?
        ) 
        and this_.start_date_time between ? and ? 
        and this_.is_suppressed=? 
        and not exists (
            select
                ee2x1_.id as y0_ 
            from
                incident i_ 
            inner join
                incident_excursion excursione3_ 
                    on i_.id=excursione3_.incident_excursion_id 
            inner join
                excursion ee2x1_ 
                    on excursione3_.excursion_id=ee2x1_.id 
            where
                (
                    ee2x1_.id=this_.id
                )
        ) 
    order by
        this_.start_date_time desc

Learning to do this stuff with criterias is awesome, as it is much easier to do custom filtering with criterias than using raw SQL. It can also be easier to read.