0

two domain classes (beans)

A and B, A has a property b of type B (-> A.b) In addition, A is the parent of an inheritance hierarchy using a single table.

I would like to join these using HQL and to preserve all Bs that are not associated with an A in the result set. If there are associations between A and B, I want only some of them based on some criteria on A.

My initial query looked like this

SELECT (a.something, b.something)
FROM A as a RIGHT OUTER JOIN a.b
WHERE a.dateCreated < someDate;

The problem with this query: a is sometimes null such that the WHERE criteria resolves to false and the tuple is not returned so that the result set does not contain Bs that are not associated to an A.

So I modified the query to something like this:

SELECT (a.something, b.something)
FROM A as a RIGHT OUTER JOIN a.b
WHERE a is null or (a.dateCreated < someDate);

This does not help as in the generated SQL, it does not seem possible to control where the class selection statement, e.g.

where a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')

is inserted; it would have to go inside the parentheses, i.e.

where a is null or (a.dateCreated < someDate and a.class in ('someChildOfA', 'anotherChildOfA', aThirdChildOfA'))

but it ends up being added outside, i.e.

where (a.id is null OR dateCreated < someDate) and a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')

Right now, I am a bit out of ideas... Any (maybe even cleaner) suggestions? If it matters, I am using Grails.

Thanks

user462982
  • 1,635
  • 1
  • 16
  • 26
  • http://stackoverflow.com/questions/2093025/how-to-perform-a-non-polymorphic-hql-query-in-hibernate shows some options on how to disable polymorphic query behavior (and hopefully this will eliminate the `a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')` statement) There is also a rough idea on how to do it on a per query basis which I need. Did somebody do something like this in Grails already? – user462982 Aug 22 '12 at 10:34

2 Answers2

2

You are basically having a right outer join with a filter in the where clause. Also since this filter is for the left side table (A), the join reduces to an inner join.

I have had the same issue and in HQL the only way to solve it is to check for null ex: Replace your where as below

where (a.id is null OR dateCreated < someDate)

I dont understand the class selection issue . This works

PrS
  • 21
  • 1
  • It'd definitely a better answer. – Victor Sergienko Sep 14 '12 at 09:55
  • Well, your proposal is just what I did above. My problem is that this is a polymorphic query, i.e. Hibernate inserts `where a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')` into the query sting, so it looks like `where (a.id is null OR dateCreated < someDate) and a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')`, which still leads to problems if a is null, because the bracketing is still not OK. – user462982 Oct 22 '12 at 15:16
1

You can not select orphaned Bs from A (where a is null) - it just makes no sense. You need a different query to select from B for that.

So simplest and probably best working approach is to issue two different queries. If it's unacceptable, try HQL UNION replacements, or retreat to SQL.

Community
  • 1
  • 1
Victor Sergienko
  • 13,115
  • 3
  • 57
  • 91
  • Why do you think it makes no sense? Maybe my description is a bit unclear. Ex.: Consider product item purchases for A and buyers for B. The purchase links to its buyer. To get an overview of purchases per buyer and also to include those buyers without any purchases, this would make sense to me... – user462982 Aug 22 '12 at 07:49
  • HQL UNION replacements are a nice idea, but finally, I need to select out of the joined A - B result set (as there is some grouping and sorting step over properties of A and B) and this seems to always include a `where a.class in ('someChildOfA', 'anotherChildOfA', 'aThirdChildOfA')` clause which removes all tuples where a is null - it seems impossible to prevent this. Maybe it is possible to tell hibernate not to include this class selection statement? – user462982 Aug 22 '12 at 10:08
  • 1) It looked to me that, in terms of your example, you were selecting purchases - sorry, I was mistaken, you're building a summary over two tables. – Victor Sergienko Aug 25 '12 at 10:27