Hey there,
I have the the following problem: the basic idea is to fetch sorted list of objects (from tableA) ordered by the price average of corresponding items from tableB (tableA 1:n TableB).
i.e. tableA has multiple items, each of which has multiple prices associated, recorded on tableB.
The SQL (below) works fine, but I'm struggling to get the same results using d criteria api.
Code:
-- **tableA**
-- tableA_id (primary)
-- names
-- ...
--
-- tableA (1:n) tableB
--
-- **tableB**
-- tableB_id
-- tableA_id (fk)
-- price
select * from tableA A
inner join
(
select tableA_id, avg(price) as avgPrice
from tableB group by tableA_id
) B
on
A.id = B.id
ORDER BY B.avgPrice
so far I tried different solution, none of them really worked. i.e.:
Code:
ProjectionList projList = Projections.projectionList();
projList.add(Projections.avg("tableB.price").as("tableB.avgPrice"));
projList.add(Projections.groupProperty("tb.tableB"));
DetachedCriteria dcrit = DetachedCriteria.forClass(TableB.class,"tb")
.setProjection(projList)
Criteria cg = getEntityManager().createQuery(TableA.class)
.createCriteria("TableB", "tb")
.add(Subqueries.exists(dcrit))
.addOrder(Order.asc("tb.avgPrice"));
The result is:
Code:
Cause: could not resolve property: avgPrice of: TableB
Any idea???
Login
FAQ
Search