2

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

Table example

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?

snieguu
  • 2,073
  • 2
  • 20
  • 39
Leandro Roura Sixto
  • 672
  • 7
  • 21
  • i think you should be able to use right-join with a where constraint where the tableB.id IS NULL. – elixir Dec 06 '17 at 18:49
  • I tried to use JoinType.RIGHT_OUTER_JOIN. However, I am using Oracle 12c and that syntax is not allowed. I got a message suggeting me the use of Oracle 9i and Oracle 10g diaclect – Leandro Roura Sixto Dec 06 '17 at 18:54

1 Answers1

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.

Joch
  • 230
  • 1
  • 9