-->
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.  [ 5 posts ] 
Author Message
 Post subject: Help with criteria query
PostPosted: Tue Jun 22, 2004 7:07 pm 
Newbie

Joined: Sat Apr 10, 2004 12:14 am
Posts: 7
I have a HQL statement:

"select distinct from Project p inner join p.projectAssignmentsInternal pa where pa.account = ?"

projectAssignmentsInternal is a property of Project that holds a collection of Assignment objects. An Assignment object has a property called account that refers to the Account object in the assignment.

The above query returns a set of distinct Project instances where the account has at least one "assignment" (i.e. permission). No big deal here. It is working perfectly.

However, I'd like to use this same "filter" within a Project Criteria query because I have many other fields in a search form I'd like to add to the filter.

For example,

Criteria crit = session.createCriteria(Project.class);
crit.setMaxResults(maxResults);
if (searchForm.getShortName() != null) {
crit.add(Expression.like("shortName", searchForm.getShortName() + "%"));
}
...
crit.addOrder(Order.desc("createdDate"));
List results = crit.list();

Basically, I want to further limit the searchable scope to include only those Projects where the inquiring account has at least one assignment (i.e. permission). Any help?

Thanks, Dan.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 22, 2004 7:16 pm 
Hibernate Team
Hibernate Team

Joined: Tue Sep 09, 2003 2:10 pm
Posts: 3246
Location: Passau, Germany
Section 12.4 of the Manual:
Code:
List cats = sess.createCriteria(Cat.class)
    .add( Expression.like("name", "F%")
    .createCriteria("kittens")
        .add( Expression.like("name", "F%")
    .list();


For your case:

Code:
List projects = sess.createCriteria(Project.class)
     .add(Expression.like("shortName", searchForm.getShortName() + "%"))
     .addOrder(Order.desc("createdDate"))
     .createCriteria("projectAssignmentsInternal")
          .add(Expression.eq("account", someAccount))
     .list();


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 23, 2004 9:33 am 
Newbie

Joined: Sat Apr 10, 2004 12:14 am
Posts: 7
I did see this in the manual and it was the first thing I tried. Unfortunately, it does not work. A segment of the error message I am receiving is:

collection was not an association: com.mydomain.Project.projectAssignmentsInternal
....

The reason I believe I am receiving this error is because the project assignments field is not a traditional one-to-many set. Here is an excerpt from the mapping file (for Project class):

<set name="projectAssignmentsInternal" table="project_assignment" cascade="all" lazy="true">
<key column="project_id"/>
<composite-element class="com.mydomain.Assignment">
<many-to-one name="account" column="account_id" cascade="none"/>
<many-to-one name="role" column="role_type_id" cascade="none"/>
</composite-element>
</set>

As you can see, I am using a "composite-element association" instead of a simple one-to-many association. This is so I can have a generic Assignment class to be used for all assignments, including ProjectAssignments, GroupAssignments, OfficeAssignments, etc.

Why does this HQL work:

"select distinct from Project p inner join p.projectAssignmentsInternal pa where pa.account = ?"

but not the Criteria query?

Any other suggestions?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 23, 2004 10:09 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Querying collections of value type is not supported in the Criteria API. Use HQL, or change your mapping to make the composite element class an entity class.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 05, 2006 3:59 am 
Newbie

Joined: Tue Aug 02, 2005 1:24 pm
Posts: 5
Sorry Gavin but I think that using composite-elements in collection mappings is quite common in applications db.
I also think that criteria query API is very useful expecially for those applications (like the one I'm working on) where many many web pages are used for searching on a database with a variable set of constraints.

Could you think about supporting composite elements also in criteria query API, or alternatively support in the Hql language some feature that can avoid using a named parameter in a query when that parameter is not specified (upon some kind of configuration of course, no as default behaviour).

Thank you very much,
Riccardo

_________________
Riccardo Palombella
Accenture S.p.a.
Milan
Italy


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 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.