Seam EntityQuery Many-to-Many Joins, Distinct, and Oracle -

i'm seam newbie in established project, lot of code use borrowed , i'm not sure how things work. problem using query object extended entityquery list page search , sort capabilities needs search across many-to-many relationship , separate many-to-one relationship must used sort. because many-to-many relationship has joined in allow search capability, query returns duplicate records each assignment. that's not big deal because added "distinct" ejbql , worked fine. however, when try order other many-to-one relationship, oracle throws error. appears oracle not accept order column not in select clause when using distinct keyword http://ora-01791.ora-code.com/, , http://oraclequirks.blogspot.com/2009/04/ora-01791-not-selected-expression.html.

here relationships defined in entities: [subject m:m jobfunction] (obviously through assignment table [subject o:m subject_jobfunction m:o jobfunction]), , [subject m:o type]. because need search subject jobfunction, joined in in ejbql requires distinct keyword return distinct subjects list page. when try order type.name (through many-to-one relationship), resulting query makes oracle angry , throws "ora-01791: not selected expression" error. subjectquery code:

@override public string getejbql() {     return "select subject subject subject left outer join subject.jobfunctions jobfunction"; }  @override @suppresswarnings("rawtypes") public list<valueexpression> getrestrictions() {     valueexpression[] restrictions = {              createvalueexpression("lower(subject.name) #{subjectquery.preprestriction(subjectquery.subject.name)}"),              createvalueexpression("subject.active = #{subjectquery.active}"),              createvalueexpression("subject.type.name = #{subjectquery.typename}"),              createvalueexpression("jobfunction.name = #{subjectquery.jobfunctionname}")             };     return arrays.aslist(restrictions);  } 

when set query order when user sorts type name through front end:

"#{subjectquery.order=='upper(subject.type.name) asc'}" 

i oracle error. if take distinct out of ejbql, sort works fine, duplicate subject records. when add distinct keyword list works fine without duplicate records, sort throws error. have suggestions how can restructure ejbql return distinct records without distinct keyword make sort happy, or how sort without making oracle angry sort column referenced in query not in select clause? have read several places answer might in the hibernate criteria api, have no idea how leverage in context of extended entityquery class trying accomplish. please help!

if adding distinct, broken.

"because many-to-many relationship has joined in allow search capability, query returns duplicate records each assignment. "

consider case person can work on many projects , project can have many persons. there uniqueness of 'person/project'. if want list of people work in either project or b (or both) may get

fred/proj_a bill/proj_a fred/proj_b tom/proj_b bill/proj_c 

if show names (not projects), can still order project, see

fred bill fred tom bill 

if distinct, can no longer order project, because don't know whether fred 1 proj_a or proj_b or whether bill comes before tom (based on proj_a) or after tom (based on proj_c).

so remove distinct , show column on ordering (because you'll see why duplicates aren't duplicates).