Grails, left outer join

Question

I have two domain classes Users and Projects like following

Users{
    String firstName
    String lastName
    String emailAddress
    static hasMany = [projects:Projects]
}



class Projects {
    String projectName
    String description
    Users projectLead
    Date completionDate
    static belongsTo = Users
}

Here completionDate == null means the project has not yet been completed.

Now I want to send an email reminder to each user about their incomplete projects, How can write a query to retrieve incomplete projects per user?

I was thinking on following lines but am still not able to go ahead. In order to send an email I will need users emailid, all incomplete projects and names of them

def users = Users.list()
       for(user in users){
           user.projects.find{it.completionDate==null}
       }

Is it possible to use createCriteria in such a case?

Answer 1

I think this should work:

def usersAndIncompleteProjects = Users.withCriteria {
    projects {
        isNull( completionDate )
    }
}

That should just return you the Users with incomplete projects, and the projects property for each User will only contain the incomplete projects. If you want the Users to have all of their projects loaded, I believe you need to use an alias

---

Testing...

Given the User class:

package criteriatest

class User {
    String name

    static hasMany = [ projects: Project ]
}

And the Project class:

package criteriatest

class Project {
    String name
    Date completionDate

    static belongsTo = User

    static constraints = {
        completionDate( nullable:true )
    }
}

This integration test passes (hopefully the asserts explain it)

package criteriatest

import grails.test.*

class UserTests extends GroovyTestCase {
    protected void setUp() {
        super.setUp()
        User.withSession { session ->
            def tim = new User( name:'tim' )
            def dave = new User( name:'dave' )

            [ tim, dave ]*.save()

            def project1 = new Project( name:'project 1', completionDate:null )
            def project2 = new Project( name:'project 2', completionDate:new Date() )

            tim.addToProjects project1
            tim.addToProjects project2

            [ project1, project2 ]*.save()

            session.flush()
            session.clear()
        }
    }

    protected void tearDown() {
        super.tearDown()
    }

    void testQuery() {
        def usersAndIncompleteProjects = User.withCriteria {
            projects {
                isNull 'completionDate'
            }
            order 'name', 'asc'
        }

        // We get two users back (users with no projects get returned as well)
        assert usersAndIncompleteProjects.size() == 2

        // First user (dave) has no projects
        assert usersAndIncompleteProjects[0].projects.size() == 0

        // Second user (tim) has one project (with a null completionDate)
        assert usersAndIncompleteProjects[1].projects.size() == 1

        // Check it's the right project
        assert usersAndIncompleteProjects[1].projects*.name == [ 'project 1' ]
    }
}

(this is the sql the criteria query executes in this instance):

select
    this_.id as id1_1_,
    this_.version as version1_1_,
    this_.name as name1_1_,
    projects3_.user_projects_id as user1_3_,
    projects_a1_.id as project2_3_,
    projects_a1_.id as id0_0_,
    projects_a1_.version as version0_0_,
    projects_a1_.completion_date as completion3_0_0_,
    projects_a1_.name as name0_0_ 
from
    user this_ 
left outer join
    user_project projects3_ 
        on this_.id=projects3_.user_projects_id 
left outer join
    project projects_a1_ 
        on projects3_.project_id=projects_a1_.id 
where
    (
        projects_a1_.completion_date is null
    ) 
order by
    this_.name asc

Answer 2

I'm not sure this problem requires a left join, unless you want to include Projects with a null user. Why not just select all projects with null completion dates and join against user?

In HQL, it would look something like this:

Projects.executeQuery('from Projects p join p.projectLead u where p.completionDate is null')

You can do similar in a criteria query:

Projects.withCriteria {
    isNull('completionDate')
    join('projectLead')
}