Hi all,

I have created a generic search component using criteria API. It works great except a few issues and one of them is proving to be a blocker for the project.

I have a class association similar to this

public class Project {


private Long id;
...
...
private Set teamMembers; // set of TeamMember type
}

public class TeamMember {

private Long id;
....
....
private String memberRole; //can be 'Manager', 'Leader', 'Developer', etc

}


Now I have show list of Projects with attibutes like name, dateStarted, Leaders, etc.

I get the list of project objects and render each object as a row. For displaying the leaders, i iterate over the teamMember collection and show name only if role == 'Leader'.

All this is workig fine but the problem is that the result set should be sortable on all displayed columns and I am not able to think of a way to provide sorting on Leaders, i.e. sort the project objects on the basis of Leader names.

Please note that the teamMember set in Project object can have :

1. no members at all.
2. 1 or more members but no leader
3. 1 or more members with one or more leaders.

Any help to resolve the issue will be highly appreciated.

rohit

I would recommend implementing special-case queries using <sql-query>/createSQLQuery for this sort of thing, as it will be vastly most efficient than using criteria. However, it is possible to do it using SQLProjection. Add an aliased SQLProjection (setProjection(Projections.projectionList().add( Projections.sqlProjection(<yourSubselectHere>), "leaderName" ))), and then addOrder("leaderName") will sort by that.

Hi tenwit,

Thanks for your response.

As I mentioned in orignal post that i have written a search component using criteria api and hence it would not be possible for me to use HQL.

I had tried sql projection but I was not successful as association can have more than one members as Leader hence the subselect in sql projection returns more than one row.

Use the sqlProjection and set your result transformer to DISINCT_ROOT_ENTITY.