Hello there,

This is to provide further detail on a post I made earlier, since I think it warrants a new thread and I'll now be able to go into more detail.

I have a parent class that has a property, say of type 'Child'. The parent also has a collection of child objects associated with it. I am wanting to perform a query whereby I provide a collection of child objects and check whether any of the children appear in either the child property or the collection of childern on the parent object through the use of the criteria objects only. I can achieve the first requirement as follows:



This would simply produce SQL akin to the following:



I cannot replace "child" (child object) with "children" (collection of child objects), though this is to be expected.

The SQL I would want to be generated in this case is something like:



I realise I may be able to change my getChildren() function such that it returns, say the IDs of the child objects then perform the following:



...however I have many of these types of queries to run and believe there must be a better way of doing this than by creating a collection/array of IDs for each collection I need to search against.

Has anybody experienced a similar issue? Am I barking up the wrong tree?!

Any help would be gratefully received. Many thanks in anticipation,

Carl

Hi again,

Having tried substituting the IDs as mentioned earlier I do get the query on the collection to work, however in doing this joins are created rather than a subquery. I am wanting to perform an 'OR' query and the join is not desirable. I understand that to perform a subquery I need to use DetachedCriteria in some way, however I am struggling to find any examples that point me in the right direction. The collection of objects contained by the parent do not have a nested parent object as they are the result of a many-to-many query which I think doesn't help matters much!

The final SQL I want to achieve (with the 'or') is as follows:



Can this be done?!

Thanks in anticipation, Carl

Hi again,

Having tried substituting the IDs as mentioned earlier I do get the query on the collection to work, however in doing this joins are created rather than a subquery. I am wanting to perform an 'OR' query and the join is not desirable. I understand that to perform a subquery I need to use DetachedCriteria in some way, however I am struggling to find any examples that point me in the right direction. The collection of objects contained by the parent do not have a nested parent object as they are the result of a many-to-many query which I think doesn't help matters much!

The final SQL I want to achieve (with the 'or') is as follows:



Can this be done?!

Thanks in anticipation, Carl

Apologies for repeated posting, but I've realised that since this is a many-to-many relationship that the SQL I want to achieve is different to what was previously mentioned. I am now considering performing this in HQL as I think it's too complicated for the criteria objects (though please somebody correct me if I'm wrong).



As mentioned before the child object has no reference to the parent object. If somebody could let me know how to achieve this in either HQL or by using the criteria objects I would be most grateful.

Cheers, Carl

Havent tried this but first thing out of my mind is sth like this

select p
from Parent p
join p.children children
where children.id in (x,y,z) or
p.child.id in (x,y,z)

I'm afraid that's not really what I'm looking for - the join would need to be a left join really, though this would not return unique results... If the collection contained 2 values that matched 2 values in a parent's child collection, that parent would be returned twice.

Thanks anyway. I have achieved this as follows in HQL, although I'm not sure if it's the best way or not!:



Thanks anyway, and if anybody can think of a better way of doing the above please let me know.

Carl