Geek asks and answers

“in all” restrictions for querying Grails / Hibernate criteria

I need a way to execute "all" criteria queries in Grails / Hibernate. The standard “in” constraint is what I would call “in any” constraint.

As an example...

Preamble

Take the Knights domain class, which has a many-to-many relationship with another class of Dangers domain. Classes are simple ...

class Knight {
  String name
  static hasMany = [perils:Peril]   
}

class Peril {
  String name
  static hasMany = [knights:Knight]
  static belongsTo = Knight
}

The contents of the table are as follows:

    Knights
    1 Arthur, King of the Britains (Not technically a knight. I know, I know.)
    2 Sir Bedevere the Wise
    3 Sir Lancelot the Brave
    4 Sir Robin the Not-Quite-So-Brave-As-Sir-Lancelot
    5 Sir Galahad the Pure

    Perils
    1 Black Knight
    2 Knights who say Ni
    3 Three-Headed Giant
    4 swamp castle
    5 Castle Anthrax
    6 Rabbit of Caerbannog

    Knights_Perils
    1, 1       // arthur fights the black knight
    1, 2       // arthur fights the knights who say ni
    2, 2       // bedevere fights the knights who say ni
    4, 3       // Robin runs-away from Three-Headed Giant
    3, 4       // Lancelot Assaults Swamp Castle
    5, 5       // Galahad visits Castle Anthrax
    3, 5       // Lancelot "rescues" Galahad from Castle Anthrax
    1, 6       // All Knights fight the Killer Bunny
    2, 6       // ...
    3, 6       // ...
    4, 6       // ...
    5, 6       // ...

, ...

    Choose Perils and Search for Knights...
     [ ] Black Knight (id 1)
     [ ] Knights who say Ni (id 2)
     [ ] Three-Headed Giant (id 3)
     [x] Swamp Castle (id 4)
     [x] Castle Anthrax (id 5)
     [x] Killer Bunny (id 6)

     [search]

:

def query = Knights.createQuery()
query.list(max: params.max, offset: params.offset) {
  if(params.perils) perils { 'in' ('id', params.list('perils')*.toLong()) }
}

, " " . " " . 3 ...

    Arthur - fights bunny
    Bedevere - fights bunny
    Lancelot - swamp castle, castle anthrax, fights bunny
    Robin - fights bunny
    Galahad - castle anthrax, fights bunny

Hibernate/Grails, "in", " ". , " " . , ,

    Lancelot - swamp castle, castle anthrax, fights bunny

"" ?

, CriteriaQuery ( Hibernate Grails), SQL HQL.

, ?

!

+3
1
. , , SQL/HQL, :
all the selected perils are in the knight perils

:

there is no peril in the selected perils that is not in the knight perils

, HQL :

select knight from Knight knight
where not exists (select peril.id from Peril peril 
                  where peril.id in :selectedPerilIds
                  and peril.id not in (select peril2.id from Peril peril2
                                       where peril2.knight.id = knight.id))
+1

More articles:


All Articles