Criteria Query - Where Collection Property = null or...

DaveOMac · **Joined:** Wed Jul 04, 2007 9:25 am **Posts:** 3

Hi Chaps,

Hopefully someone can help me out :)

I have an object with a collection of objects as a property, so lets say:

Code:

class ContainerObject
{
    public int Id;
    public IList<InnerObject> InnerObjects;
}


class InnerObject
{
    public int Id;
}

I'm trying to write a criteria query to return ContainerObjects where the collection of InnerObjects is empty or contains an InnerObject with id of 1.

I can do the parts of this query individually:

Empty Collection

Code:

IList containerObjects = sess.CreateCriteria(typeof(ContainerObject))
    .Add( Expression.IsEmpty( "InnerObjects" ) )
    .List();

Id in collection

Code:

IList containerObjects = sess.CreateCriteria(typeof(ContainerObject))
    .CreateCriteria( "InnerObjects" )
        .Add( Expression.Eq( "Id", 1 ) )
    .List();

I don't know how to do both parts in an OR so either the collection is empty or it contains an object with id of 1.

Can anyone help?

Thanks muchly

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

With criteria, it is complicated because you would have to create 2 aliases into a disjunction. I don't know if that is possible.
This code achieves the same, using HQL instead.

Code:

        StringBuffer sb=new StringBuffer();
        sb.append("select a.id from ContainerObject a \n");
        sb.append("left join a.InnerObjects b \n");
        sb.append("where a.InnerObjects is empty \n");
        sb.append("or b.value=1 \n");
        
        Query query=session.createQuery(sb.toString());
        List<A> result=query.list();

Notice how I can alias the collection of 'InnerObjects" as "b", for the comparison with 1, or keep calling it "InnerObjects", according to the container's property, for the emptiness test.

thinkpad · **Joined:** Fri Apr 11, 2008 1:48 am **Posts:** 36

DaveOMac wrote:

I can do the parts of this query individually:

Empty Collection

Code:

IList containerObjects = sess.CreateCriteria(typeof(ContainerObject))
    .Add( Expression.IsEmpty( "InnerObjects" ) )
    .List();

Does this solution work? I got the problem, that the criteria object seems not to recognize an empty collection.
Don't you have to set an alias or an fetchmode to InnerObjects?

You can work with createAlias to join two properties of different entities:

Code:

IList containerObjects = sess.CreateCriteria(typeof(ContainerObject))
    .createAlias("InnerObjects","io")
    .add(Restrictions.or(Restriction.eq("io.id", 1), Restriction.isEmpty("io"))
    .List();

I am just wondering, why Restriction.isEmpty() does not work in my example:
http://forum.hibernate.org/viewtopic.ph ... 71#2383971

cws · **Joined:** Sat Sep 22, 2007 5:42 am **Posts:** 10

Hello!

Have you checked the generated sql? That would be intresting.

I ran into kind of the same problem, but solved it with a detached criteria and the subqueries namespace.

But this that you are describing is a more elegant way.

/C-W

thinkpad · **Joined:** Fri Apr 11, 2008 1:48 am **Posts:** 36

Hi,
how can I check the generated SQL statement?

cws · **Joined:** Sat Sep 22, 2007 5:42 am **Posts:** 10

You could set up log4net with nhibernate and set the property show_sql or something like that to true

see documentaion 3.6. Logging and 3.8. XML Configuration File for the log4net.

You could also use the monitor tool of sql server management or likewise depending on which database you are using.

Also check http://forum.hibernate.org/viewtopic.php?t=986336 thread in the nhibernate forum about subqueries.

Regards
/C-W