Grails create criteria not in list

Question

how to get list A where not use in domain B?

Domain A

Class A{
String name
String code
}

Domain B

Class B{
A aaa
String description
}

example data:

**domain A**

id+versioin+name   +code+|
1 | 0      |Bobby  |bob  |
2 | 0      |anto   |ant  |
3 | 0      |Jessica|jes  |
4 | 0      |hera   |her  |

**domain B**    
id+version|a_id|description + |
1 | 0     | 1  |this is bobby |
2 | 0     | 3  |this is jessic|

how can i get list from A where not used in B.

i tried this
def b = B.list()
def c = A.createCriteria()
def results = c.list {
    not { 'in'(b) }
}

but fail..

1

try def b = B.list()*.a – uladzimir May 19 '14 at 07:56

score 8 · Accepted Answer · answered May 19 '14 at 09:06

8

def b = B.list()
def c = A.createCriteria()
def results = c.list {
    not { 'in'("id",b*.aaa.id) }
}

answered May 19 '14 at 09:06

The performance of this will not be as good as the solution that works in Grails 2.4 as above, because you are performing 2 queries instead of one. Also if you `aaa` association lazy you may have a N+1 problem. – Graeme Rocher Jun 05 '14 at 21:48
then whats the solusion? – Jun 06 '14 at 04:26
but i'm using grails 2.1.1 – Jun 06 '14 at 04:28

score 2 · Answer 2 · answered May 19 '14 at 12:21

2

For completeness, note in Grails 2.4 (which is at the RC phase and about to go GA) you can write this query as a single query using a subquery, which will perform better:

  def c = A.createCriteria()
  def results = c.list {
      notIn new DetachedCriteria(B).id
  }

answered May 19 '14 at 12:21

Graeme Rocher

7,985
2
26
37

is this still the preferred solution with grails 3 -- or there's another better solution ? – Sudhir N May 23 '17 at 05:50

Grails create criteria not in list

2 Answers2