Is there a "not in" equivalent in GORM?

Question

Can this be converted to createCriteria ()?

SELECT * FROM node WHERE (node.type = 'act' AND nid NOT IN (SELECT nid FROM snbr_act_community)) LIMIT 10 

I know that there is an "in" operator, and here is what I still have:

 def c = VolunteerOpportunity.createCriteria() def matchingActs = c.list { node { eq('type', 'act') } maxResults(10) } 

I just want to see if this is possible. Otherwise, I think it is possible in HQL right?

Answer 1

Thanks to Sammyrules for the code. I got an idea from this. tested it, but it did not work. I fixed it and here is the final working code:

 def ids = [14400 as long, 14401 as long] def c = VolunteerOpportunity.createCriteria() def matchingActs = c.list { node { eq('type', 'act') not { 'in'(ids) } } maxResults(10) } 

Now I know how to use the "not" operator. Thank you so much!

Answer 2

I haven’t tried it myself, but looking at the Grails and hibernate api docs, you create nodes on this builders map with static methods found in the Hibernate API restriction class. 1 . So something like

  def c = VolunteerOpportunity.createCriteria() def matchingActs = c.list { node { not(in('propertyName', ['val1','val2'])) } maxResults(10) } 

Since you are binding the in method (returning the criteria) using the not method (which takes the Criterion parameter as an argument and returns a negative version)

Answer 3

this solution:

 def resultat=EnteteImputationBudgetaire.createCriteria().get{ between("dateDebutPeriode", dateDebut, dateFin) and{ eq 'natureImputationBudgetaire','FONCTIONNEMENT' } maxResults(1) } def resultat2=ParametragePlanBudgetaire.createCriteria().list() { like('composantBudgetaire','6%') if(resultat?.details) { not { 'in'('composantBudgetaire',resultat?.details?.imputationBudgetaire) } } } 

Answer 4

According to Grails documentation on creating criteria here , you can use something like this:

 not {'in'("age",[18..65])} 

In this example, you have a property called "age" , and you want to get strings that are NOT between 18 and 65. Of course, part [18..65] can be replaced with any list of values ​​or range that you need.

Answer 5

Just remember: in this case you do not need to use parentheses, and you can use inList , for example:

 not { inList 'age',[18..65] }