Hi there,
I am having a problem with a requirement to make a query returning some statistics (count grouped by some fields). When I create a Criteria query like this:
Code:
Projection projections = Projections.projectionList() //
.add(Projections.groupProperty("tx.owner").as("user"))//
.add(Projections.groupProperty("tx.ownerUnit").as("orgUnit"))//
.add(Projections.groupProperty("ci.assignedPhase").as("phase"))//
.add(Projections.groupProperty("tx.soldProduct").as("product"))//
.add(Projections.count("tx.soldProduct").as("count"));
Criteria crit = getMainCriteria("tx")//
.setProjection(projections)//
.add(Restrictions.eq("status", TransactionStatus.ACCEPTED))//
.setResultTransformer(new AliasToBeanResultTransformer(ProdStatusEntry.class))//
.createCriteria("challengeItemRegistered", "ci").createCriteria("assignedPhase", "ph")//
.add(Restrictions.in("id", phaseIds));
I get exactly what I want - beans (set by projections "user", "orgUnit" etc) are inserted into my ProdStatsEntry bean (set by result transformer) as fully lazy initialized proxies - query asks only for IDs of those objects:
Code:
select
this_.fk_owner_id as y0_,
this_.fk_owner_unit_id as y1_,
ci1_.fk_assigned_phase_id as y2_,
this_.fk_sold_product_id as y3_,
count(this_.fk_sold_product_id) as y4_
from
ppl_transaction this_
inner join
ppl_challenge_item ci1_
on this_.fk_chng_item_registered_id=ci1_.id
inner join
ppl_challenge_phase ph2_
on ci1_.fk_assigned_phase_id=ph2_.id
where
this_.status=?
and ph2_.id in (
?
)
group by
this_.fk_owner_id,
this_.fk_owner_unit_id,
ci1_.fk_assigned_phase_id,
this_.fk_sold_product_id
but object are fully functional lazy-initialized proxies, if I ask for any property of user/unit/phase objects it'll be grabbed from a database.
The problem is in a similiar, but more complicated case. I need to create a bit more complicated stats-related query and it's to complicated (for me) to use criteria on it:
Code:
String maxDatePerTripleQuery = //
"( SELECT max(maxSah.dateFrom) FROM SellerAssignHistory maxSah WHERE "//
+ " maxSah.seller = pr.balanceOwner AND maxSah.chPhase = pr.requestedPrize.phase "
+ " AND maxSah.dateFrom <= pr.requestedDate )";
String groupQuery = //
"SELECT count(pr) AS prizeCount, user, phase, prize, unit "//
+ "FROM PrizeRequest pr, UserSID user, ChallengePhase phase, Prize prize, OrganisationalUnit unit " //
+ "WHERE user.id = pr.balanceOwner.id AND " //
+ " phase.id = pr.requestedPrize.phase.id AND "//
+ " prize.id = pr.requestedPrize.prize.id AND "//
+ " unit.id = sah.belongsTo.id AND "//
+ " sah.id = " //
+ " (SELECT sah2.id FROM SellerAssignHistory sah2 " //
+ " WHERE sah2.seller = pr.balanceOwner AND sah2.chPhase = pr.requestedPrize.phase "
+ " AND sah2.dateFrom = " + maxDatePerTripleQuery + " ) " //
+ "GROUP BY user, phase, prize, unit";
Query query = getSession().createQuery(//
groupQuery);
It SEEMS to me, that it's a bit similar to the one created by criteria - in terms of grouping and projecting results... This query works fine in general (SQL is valid on MySQL and seems to return proper values in terms of business meaning):
Code:
select
count(prizereque0_.id) as col_0_0_,
prizereque0_.fk_balance_owner_id as col_1_0_,
prizeforch3_.fk_phase_id as col_2_0_,
prizeforch3_.fk_prize_id as col_3_0_,
sellerassi1_.fk_belongs_to_id as col_4_0_,
usersid2_.id as id23_0_,
challengep4_.id as id4_1_,
prize6_.id as id7_2_,
organisati7_.id as id6_3_,
usersid2_.fk_belongs_to_id as fk7_23_0_,
usersid2_.created_at as created2_23_0_,
usersid2_.display_name as display3_23_0_,
usersid2_.ldap_id as ldap4_23_0_,
usersid2_.fk_management_org_unit_id as fk8_23_0_,
usersid2_.sid as sid23_0_,
usersid2_.technical_user as technical6_23_0_,
challengep4_.active_phase_start as active2_4_1_,
challengep4_.challenge_id as challenge3_4_1_,
challengep4_.closed_phase_start as closed4_4_1_,
challengep4_.fk_description_id as fk14_4_1_,
challengep4_.finalizing_phase_start as finalizing5_4_1_,
challengep4_.grading_scale_type as grading6_4_1_,
challengep4_.name as name4_1_,
challengep4_.prep_for_summarize_phase_start as prep8_4_1_,
challengep4_.prize_qualif_threshold as prize9_4_1_,
challengep4_.sales_plan_type as sales10_4_1_,
challengep4_.status as status4_1_,
challengep4_.summarize_phase_start as summarize12_4_1_,
challengep4_.threshold_type as threshold13_4_1_,
prize6_.image_names as image2_7_2_,
prize6_.fk_description_id as fk7_7_2_,
prize6_.name as name7_2_,
prize6_.status as status7_2_,
prize6_.param_name as param5_7_2_,
prize6_.param_value as param6_7_2_,
organisati7_.business_category as business3_6_3_,
organisati7_.ldap_id as ldap4_6_3_,
organisati7_.name as name6_3_,
organisati7_.fk_parent_unit_id as fk6_6_3_,
organisati7_.status as status6_3_,
organisati7_.structure_type as structure1_6_3_,
organisati7_.type as type6_3_,
organisati7_.fk_beneficiary_id as fk10_6_3_,
organisati7_.fk_management_org_unit_id as fk9_6_3_
from
ppl_prize_request prizereque0_
inner join
ppl_usersid usersid2_
on prizereque0_.fk_balance_owner_id=usersid2_.id,
ppl_prize_for_challenge_phase prizeforch3_
inner join
ppl_challenge_phase challengep4_
on prizeforch3_.fk_phase_id=challengep4_.id
inner join
ppl_prize prize6_
on prizeforch3_.fk_prize_id=prize6_.id,
ppl_seller_assign_history sellerassi1_
inner join
ppl_organisational_unit organisati7_
on sellerassi1_.fk_belongs_to_id=organisati7_.id
where
prizereque0_.fk_requested_prize_id=prizeforch3_.id
and sellerassi1_.id=(
select
sellerassi8_.id
from
ppl_seller_assign_history sellerassi8_
where
sellerassi8_.fk_seller_id=prizereque0_.fk_balance_owner_id
and sellerassi8_.fk_ch_phase_id=prizeforch3_.fk_phase_id
and sellerassi8_.date_from=(
select
max(sellerassi10_.date_from)
from
ppl_seller_assign_history sellerassi10_
where
sellerassi10_.fk_seller_id=prizereque0_.fk_balance_owner_id
and sellerassi10_.fk_ch_phase_id=prizeforch3_.fk_phase_id
and sellerassi10_.date_from<=prizereque0_.requested_date
)
)
group by
prizereque0_.fk_balance_owner_id ,
prizeforch3_.fk_phase_id ,
prizeforch3_.fk_prize_id ,
sellerassi1_.fk_belongs_to_id
The problem? ORACLE complains about fields in SELECT clause not being in the GROUP BY clause. So the question is:
how to force the latter HQL query to get only IDs of those object I'm grouping by and return lazy-initialized proxies?
I would really appreciate any assistance...
Login
FAQ
Search