Querying a many to many join table with HQL

Question

I need some advice for creating a hql query.

Situation: I have set of Nodes to which can be assigned a configurable amount of Flags. To do this I have the following classes/tables:

Classes:

class Node {
  String name
}

class Flag {
  String name
}

class NodeHasFlag {
  Node node
  Flag flag
  boolean value
}

Resulting tables with some sample data:

Node
id, name
1,  'a'
2,  'b'
3,  'c'
...

Flag
id, name
1,  'visible'
2,  'special'
...

NodeHasFlag
node_id, flag_id, value
1,       1,       true      // node 'a' is visible
2,       1,       false     // node 'b' is not visible
2,       2,       true      // node 'b' is special
3,       1,       false     // node 'c' is not visible
...

Now I need a hql query to get a list of nodes based on flags.

Like: Give me all nodes that are visible and special

Or: Give me all nodes that are visible and have an undefined value for special (no entry in NodeHasFlag table)

Checking for a single flag is easy but checking for multiple flags at same time is causing me trouble.

I'am using Grails and Gorm but I think the problem would be the same with standard Java Hibernate

Answer 1

I think you can solve this with subqueries. The first example might look like this. The second example would need LEFT JOIN and an OR IS NULL restriction.

    select n from Node n
        where n.id in 
            (select n2.id from Node n2 
                join n2.flags f2 
                where f2.visible = :visibleValue)
       and n.id in
            (select n3.id from Node n3
                join n3.flags f3
                where f3.special = :specialValue)