CODEWITHSUNDEEP
sqlhibernate
Stefano Vercellino
Stefano Vercellino

Reputation: 353

ORA-01791 on Hibernate Query

My code is:

Criteria criteriaA = getHibernateSession().createCriteria(entityA.class, "aaa");
Criteria criteriaB = criteriaA.createCriteria("entityB").setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); // It seems that the result transformer don't affect the code...

criteriaA.add( -- some restriction --)

if (orderById) {
    criteriaB.addOrder(Order.desc("id"));
}

if (!orderById && orderByDate) {
    criteriaB.addOrder(Order.desc("date"));
}

criteriaA.setProjection(Projections.distinct(Projections.property("entityB")));

entityA and entityB have a oneToMany relationshiop (one entityB may have 0..* entityA) but I can navigate only to A from B (project restrictions.. cannot change that).
Now, the problem is that I need no duplicate in the results returned, and I need to sort my result.
The strange fact is that the ORA-01791 (that tells me that there's an incorrect orderby) I launched only when sorting by date, sorting by id don't gave me any problem but both are property of the entity I want to distinct!
Why I can sort by id but not by date??
Is there another way to do what I'm trying to do?

---- EDIT -----
As maby suggested I looked at the generated query:

DEBUG [org.hibernate.SQL]
select distinct this_.BBB_ID as y0_
from TB_ENTITY_A this_ 
inner join TB_ENTITY_B entityB_ on this_.BBB_ID=entityB_.BBB_ID 
where this_.ANOTHER_ID=? and lower(this_.AAA_VALUE) like ? and this_.AAA_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
order by entityB_.BBB_DATE asc

so, my distinct where applied only on the id of the foreign key..
How could I force Hibernate to select all the field?
If I do:

criteriaB.list();

instead of

criteriaA.list():

will I lose the filter from criteriaA?
I was thinking also to try to set in Projection all the field from the entityB but I'm not sure hibernate can convert the result to the entity.

Upvotes: 1

Views: 1274

Answers (4)

Stefano Vercellino
Stefano Vercellino

Reputation: 353

As mabi suggested I reply to my questions to post my solution:

---- SOLVED -----

I changed the way I was organizing the query: I've create a detached criteria for the many-side entity and retrieve the other entity with a subquery:

DetachedCriteria criteriaA = DetachedCriteria.forClass(EntityA.class, "aaa");
criteriaA.add( -- some restriction --);
criteriaA.setProjection(Projections.property("entityB"));

Criteria criteriaB = getHibernateSession().createCriteria(EntityB.class, "bbb");
criteriaB.add(Property.forName("id").in(criteriaA));

so I don't need a distinct and can sort for all the field i want from entity B.

Upvotes: 1

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

mabi is correct in his/her guess.

What you want to do is to produce a query that looks something like this:

select this_.BBB_ID as y0_, min(entityB_.BBB_DATE) as min_date
from TB_ENTITY_A this_ 
inner join TB_ENTITY_B entityB_ on this_.BBB_ID=entityB_.BBB_ID 
where this_.ANOTHER_ID=? and lower(this_.AAA_VALUE) like ? 
  and this_.AAA_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
group by this_.BBB_ID
order by min_date asc

Don't know how to do it in Hibernate though.

If however Hibernate can't manage with two columns returned for fetching the ID, a query like this should work:

select y0_ 
    from(
        select this_.BBB_ID as y0_, min(entityB_.BBB_DATE) as min_date
            from TB_ENTITY_A this_ 
                inner join TB_ENTITY_B entityB_ on this_.BBB_ID=entityB_.BBB_ID 
            where this_.ANOTHER_ID=? and lower(this_.AAA_VALUE) like ? 
              and this_.AAA_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            group by this_.BBB_ID
    ) a
order by min_date asc

But again, how to do it in Hibernate, I have no clue. It think it is possible to add custom SQL/HQL though, so you should be able to create it somehow.

Upvotes: 0

sinuhepop
sinuhepop

Reputation: 20297

Strange problems occur when you use SQL keywords for table or field names. That's probably what happens here. Just try to put a @Column(name = "date_") on your entity attribute.

Upvotes: 0

mabi
mabi

Reputation: 5307

http://ora-01791.ora-code.com/ says:

In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions

I hazard a guess that you/hibernate don't select date. If you do, can you add the generated SQL to the question?

Upvotes: 2

Related Questions