CODEWITHSUNDEEP
sqlhibernategrailshqlgrails-orm
micha
micha

Reputation: 49552

Querying a many to many join table with HQL

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

Upvotes: 0

Views: 3284

Answers (2)

Jim Sosa
Jim Sosa

Reputation: 628

You could also get creative (assuming NodeHasFlag never has the same node_id and flag_id) and try something like this:

// Both visible and special
select n from Node n
   where 3 = (
     select SUM(CASE f.name WHEN 'visible' THEN 2 WHEN 'special' THEN 1 ELSE 0 END)
      from n.flags f))

or 

// Visible but not special
select n from Node n
   where 2 = (
    select SUM(CASE f.name WHEN 'visible' THEN 2 WHEN 'special' THEN 1 ELSE 0 END)
      from n.flags f))

A little more optimized but less intuitive :)

Upvotes: 1

carbontax
carbontax

Reputation: 2184

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)

Upvotes: 3

Related Questions