Help with criteria query

dan19460 · **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.

michael · **Posted:** Tue Jun 22, 2004 7:16 pm

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();

dan19460 · **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?

gavin · **Posted:** Wed Jun 23, 2004 10:09 am

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.

etr480 · **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