CODEWITHSUNDEEP
oracle-databasemany-to-manyseam
Christian Desserich
Christian Desserich

Reputation: 592

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

I'm a Seam newbie in an already established project, so a lot of code I use is borrowed and I'm not always fully sure how things work. My problem is that I am using a query object extended from EntityQuery to back a list page with search and sort capabilities that needs to search across a many-to-many relationship and a separate many-to-one relationship which must also be used to sort. Because the many-to-many relationship has to be joined in to allow for the search capability, the query returns duplicate records for each assignment. That's not a big deal because I just added "distinct" to the ejbql and that worked fine. However, when I try to order by the other many-to-one relationship, Oracle throws an error. It appears that Oracle will not accept an order by column that is not in the select clause when using the distinct keyword http://ora-01791.ora-code.com/, and http://oraclequirks.blogspot.com/2009/04/ora-01791-not-selected-expression.html.

Here are the relationships as they are defined in the entities: [Subject m:m JobFunction] (obviously through an assignment table [Subject o:m Subject_JobFunction m:o JobFunction]), and [Subject m:o Type]. Because I need to search Subject by JobFunction, it is joined in in the ejbql which requires the distinct keyword to only return distinct Subjects to the list page. When I try to order by the Type.name (through the many-to-one relationship), the resulting query makes Oracle angry and throws the "ORA-01791: not a SELECTed expression" error. SubjectQuery code:

@Override
public String getEjbql() {
    return "select subject from Subject subject left outer join subject.jobFunctions as jobFunction";
}

@Override
@SuppressWarnings("rawtypes")
public List<ValueExpression> getRestrictions() {
    ValueExpression[] RESTRICTIONS = { 
            createValueExpression("lower(subject.name) like #{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 I set the query order when a user sorts by the Type name through the front end:

"#{subjectQuery.order=='UPPER(subject.type.name) asc'}"

I get the Oracle error. If I take the distinct out of the ejbql, the sort works fine, but I get duplicate Subject records. When I add the distinct keyword the list works fine without duplicate records, but the sort throws an error. Does anyone have any suggestions about how I can restructure the ejbql to return distinct records without the distinct keyword to make the sort happy, or how to do the sort without making Oracle angry that the sort column referenced in the query is not in the select clause? I have read several places that my answer might be in the the Hibernate Criteria API, but I have no idea how to leverage it in the context of an extended EntityQuery class with what I am trying to accomplish. Please Help!

Upvotes: 0

Views: 855

Answers (2)

Christian Desserich
Christian Desserich

Reputation: 592

I'm not sure how the generated query(ies) is(are) different, but I found an answer. I wasn't aware of the fetch command for hibernate, which fixes the need for the distinct keyword (again, not sure exactly how, maybe by subquerying?). After changing the ejbql to:

@Override
public String getEjbql() {
    return "select subject from Subject subject left join fetch subject.jobFunctions jobFunction";
}

the distinct is no longer needed and therefore, Oracle does not complain about the order by column not being in the select clause. The list works as expected and the sort column works! Yay!

Predictably, I found the answer here on stackoverflow. The question was not exactly the same, but the hql syntax worked for me: HQL order by within a collection

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

If you are adding a DISTINCT, then something is broken.

"Because the many-to-many relationship has to be joined in to allow for the search capability, the query returns duplicate records for each assignment. "

Consider the case that a person can work on many projects and a project can have many persons. There is a uniqueness of a 'person/project'. If you want a list of people that work in either project A or B (or both) then you may get

FRED/PROJ_A
BILL/PROJ_A
FRED/PROJ_B
TOM/PROJ_B
BILL/PROJ_C

If you only show the names (not the projects), you can still order by project, but you will see

FRED
BILL
FRED
TOM
BILL

If you do a DISTINCT, you can no longer order by project, because you don't know whether the FRED is the one from PROJ_A or PROJ_B or whether BILL comes before TOM (based on PROJ_A) or after TOM (based on PROJ_C).

So remove the DISTINCT and always show the column on which you are ordering (because then you'll see why the duplicates aren't actually duplicates).

Upvotes: 0

Related Questions