-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Criteria + Projection equivalent of a HSQL tuple query
PostPosted: Wed Aug 01, 2007 2:49 am 
Newbie

Joined: Wed Aug 01, 2007 2:19 am
Posts: 1
Hi all,

We have a query conceptually similar to the following:

Code:
String hsql = "SELECT c.name, k FROM Cat c INNER JOIN c.kittens k WHERE c.colour in (:colours) and k.whiskerLength > :length";
// populate 'colours' with a collection, and 'length' with an int


which returns a List of 2-element array tuples, one containing the String (the name) and one containing an individual Kitten entity. If a Cat has more than one Kitten, we get multiple rows with the same 'name' (this is what we want).

The problem is, we want to change this into a Criteria query, but I am unable to work out how to produce the same result.

We can do this:

Code:
Criteria crit = session.createCriteria(Cat.class)
   .createAlias("kittens", "k")
   .add(Restrictions.in("colour", colours))
   .add(Restrictions.gt("k.whiskerLength", length));
   .setProjection(Projections.projectionList()
      .add(Projections.property("name"))
// What goes here?
// Not this:
//     .add(Projections.property("kittens"))
// nor this:
//     .add(Projections.property("k"))
   );


I'm not aware of any way to get a Projection which returns the complete 'Kitten' entity, in this case. If we use 'kittens', it comes out as null (not 100% sure why, I'll admit), but that's clearly not what we want anyway ('kittens' implies ALL kittens of each cat; we just want the particular kitten that came out of the join).

We can use the ALIAS_TO_ENTITY_MAP transformer instead of projecting, I guess, but that would involve selecting out all of the fields for the Cat class (not just 'name'): in this case, that's quite expensive and we'd really like to avoid it.

Is there something in the Projection framework that will deal with this situation?

(If it helps, the reason for the change is that in some circumstances, the 'Restrictions.in("colour", colours)' creates a massive query -- think thousands of colours -- which SQL Server rejects for having too many parameters. We're replacing, in this case, Restrictions.in()'s InExpression with a new Expression, which uses a temporary-table-like solution to work around this limitation. That works fine, it's just the projection of the result that's causing me grief. If anyone can think of a good way to do something similar using the HQL approach, then that might work too)

Thanks in advance for your help.


Top
 Profile  
 
 Post subject:
PostPosted: Mon May 05, 2008 8:29 am 
Newbie

Joined: Tue May 10, 2005 6:15 am
Posts: 4
Any solution on that? I searched the whole forum but cannot find any.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.