How to obtain the Table A rows that are not present in Table B with GORM without using an inList closure?

Question

I have a domain definition which generates the following example for database table:

I want to do the following query: Obtain all domain objects related to Table A that are not present in Table B

I was using the inList closure as follow:

    List<DMiembro> m = DMiembro.list()

    List<DUsuario> usuarios = DUsuario.createCriteria().list(params) {
        if (m) {
            not {
                m*.usuario.id.collate(1000).each { def lista ->
                    or {
                        inList("id", lista)
                    }
                }
            }
        }
        eq("enabled", true)
        order("nombre", "asc")
    } as List<DUsuario>

This works fine. However, data in DMiembro is growing to big. Now I am getting a database error related to the size of the query. And that's logical, because the number of parameters of the query is too large

So, is there another way to build a query for gathering Dusuario objects that are not present in DMiembro?

Answer 1

Solution 1: You can use GORM DetachedCriteria inside your criteria.

import grails.gorm.DetachedCriteria
List<DUsuario> usuarios = DUsuario.createCriteria().list(params) {
      not {
          'in'('id', new DetachedCriteria(DMiembro).build {
               projections {
                   property 'id'
               }
           })
      }
      eq("enabled", true)
      order("nombre", "asc")
} as List<DUsuario>

As you can see, this will be a NOT IN SQL request, which isn't a good idea for performances. However, the request is more "understandable" and is still a good solution, no matter the volumetry (except for the request's execution time).

Solution 2: Avoid the NOT IN operator with a LEFT JOIN or RIGHT JOIN and a NULL check on DMiembro table. See here for a solution with a left join as plain SQL.