I have a table called groups with four [relevant] columns; id, parent_id, activated_date and type. parent_id and activated_date are nullable, and a group is considered to be activated if it has an activated_date prior to today, is of type typeA, or its parent group is activated.

I am trying to write a query that will list all of the activated groups, and I'm running into a very confusing issue. (Note: I am using CASE statements because in JPA conditional expressions are not allowed in SELECT clauses, but they are inside of CASE expressions)

If my WHERE clause looks like:

(CASE
  WHEN
    g.activated_date is not null AND
    g.activated_date < CURDATE()
    THEN TRUE
  WHEN
    g.group_type = 'typeA'
    THEN TRUE
  ELSE FALSE
END) = TRUE

Then I find 9000 activated groups (or 1000 deactivated groups if I change to FALSE). This is expected, there are a total of 10,000 groups.

BUT when I add an additional condition to check the parent's status (Note: this does not need to be recursive, groups cannot have 'grandparents' in this use case):

(CASE
  WHEN
    g.activated_date is not null AND
    g.activated_date < CURDATE()
    THEN TRUE
  WHEN
    g.group_type = 'typeA'
    THEN TRUE
  WHEN
    (g.parent.activated_date is not null AND g.parent.activated_date < CURDATE()) OR
    (g.parent.group_type = 'typeA')
    THEN TRUE
  ELSE FALSE
END) = TRUE

Then I am suddenly finding I have 8500 activated groups (and 750 deactivated). I cannot account for the missing 750 groups; am I doing something obviously wrong?

CodePudding user response：

Your full query isn't clear, but if 'g' instances don't have parents, they are removed from the query results. You would need to use a left join on this relationship to have them included in results.

"select <your select clause> where G g left join g.parent parent where <your Where clause>"

Where ever you were using g.parent in your select or where clauses you then need to just use parent. So

(CASE
  WHEN
    g.activated_date is not null AND
    g.activated_date < CURDATE()
    THEN TRUE
  WHEN
    g.group_type = 'typeA'
    THEN TRUE
  WHEN
    parent is not null AND
    ((parent.activated_date is not null AND parent.activated_date < CURDATE()) OR
    (parent.group_type = 'typeA'))
    THEN TRUE
  ELSE FALSE
END) = TRUE