Hi,

Consider there are 3 classes representing 3 database tables: Student, Course and StudentCourse(associative relationship).

The source code bellow returns all students from the associative table that conform to some restrictions (ommited for clarity)



As I need to return the students from the association StudentCourse I'm using a projection here and it is working. The problem is that I need to order this projection based on the student name and I failed on all my attempts. Considering that the Student class has a name attribute, what I need to do to order the results based on this attribute?

Thanks in advance!

Is it that hard?

try this,

criteria..addOrder( Order.asc("objectAlias.proprtyName") )

try this,

criteria..addOrder( Order.asc("objectAlias.proprtyName") )

Thanks for the reply but unfortunatelly it didn't work. If I add the order statement, the execution ends up in a SQLGrammarException exception - "ORA-01791: not a SELECTed expression". This is the SQL that is generated:


Anything else?

Hey, try overriding compareTo() method in your class and compare based on the "student" property. All orders must preferably done in memory and not increase the burden on the database...

Do not forget to rate this post if this answers your question.

==============================

Shreenath Sreenivas
Senior Software Engineer (TL)
Ocimum Biosolutions

Hi Shreenath,

Thanks for the answer. I have already tried to override compareTo, but unfortunately it didn't work either. I'm sending the order instruction to the database because I'm using setFirstResult and setMaxResults to restrict the search results. So, before getting a subset of the results, I need to order them.

This query is not complex, there should be a way to order the results based on a field of the projection. The problem is that I can't see this way :)

Any other tip? Maybe switching from Criteria to HQL?

Thanks!

Hi,

************************************************
ORA-01791: not a SELECTed expression
Cause: There is an incorrect ORDER BY item. The query
is a SELECT DISTINCT query with an ORDER BY clause. In
this context, all ORDER BY items must be constants,
SELECT list expressions, or expressions whose operands
are constants or SELECT list expressions.
Action: Remove the inappropriate ORDER BY item from
the SELECT list and retry the statement.
************************************************

Alternative Solution : Project the column which you wish to order in
select clause also. Use aliases if required (many-to-one) -
criteria.createAlias(). This would allow you to
project fields on associated entities (many-to-one
etc.). If it is a <join>, you need to use aliases.

Criteria criteria=
session.createCriteria(StudentCourse.class);
criteria.createAlias("log", "logAlias");

criteria.createCriteria("student", "s");

// add some restrictions on s and c

Projection list = Projections.projectionList();

list.add(Projections.property("student"))
list.add(Projections.property(""logalias.name""))

criteria.setProjection(Projections.distinct(list));

return criteria.list();

Your query should then look like this:

select distinct this_.CD_STUDENT, log1_.NAME as
y0_ from STUDENT_COURSE this_
inner join STUDENT log1_ on this_.CD_STUDENT
=log1_.CD_STUDENT
order by log1_.NAME asc


Do not forget to rate this post if this answers your
question.

==============================

Shreenath Sreenivas
Senior Software Engineer (TL)
Ocimum Biosolutions

Hi again Shreenath,

You are absolutely right! You suggestion did the trick and that problem is in the past now :)

Thank you very much!!

Best Regards,
Cleverson Schmidt