Grails query: Get list of associated objects

Question

I have a many-to-many relationship:

class Project {
    Set<PrincipalInvestigator> pis
     :

    static hasMany = [ pis:PrincipalInvestigator ]
}

class PrincipalInvestigator {
    String name
     :
}

I want a query that returns a unique and sorted list of PIs that belong to a pre-defined list of projects.

A naive approach is to iterate thru the projects, and iterate thru their list of PIs, while removing the dupes. The code to do this is trivial, but it is slow.

So far, the best working solution I could come up with is:

def pi_ids = Project.createCriteria().list{ // find unique list of PI IDs
    // project filters here, not relevant to the question
    createAlias('pis', 'aka_pis', JoinType.LEFT_OUTER_JOIN)
    isNotNull('aka_pis.id')
    projections {
        distinct('aka_pis.id')
    }
}
def pi_list = PrincipalInvestigator.createCriteria().list{ // get PIs from list of IDs
    inList('id', pi_ids)
    order('name', 'asc')
}

My solution is one order of magnitude faster, but it's still 2 distinct queries. Is there a way to get the same result in a single query?

Answer 1

The solution to my problem is this HQL:

PrincipalInvestigator.executeQuery(
  "select distinct pi from Project p inner join p.pis as pi where p.id in :projectIds order by pi.name",
  [projectIds:[1,2,3]])

This solution allows for sorting of the distinct results and the inner join trims all the null instances. Thanks to cfrick for putting me on the right track.