CODEWITHSUNDEEP
javahibernatehql
Xiezi
Xiezi

Reputation: 2989

HQL with multiple tables joining and sum(), first()

Talbes:

Grandpa
    GP_ID  (PK)
    NAME

Dad
    DAD_ID (PK)
    PG_ID  (FK)

Children
    C_ID   (PK)
    DAD_ID (FK)
    MONEY_AMOUNT
    MONEY_UNIT

Class (annotations omitted here):

class Grandpa
{
    Long id;
    String name;

    Double moneyAmount; // sum of Children's money amount

    String moneyUnit; // first of Children's money unit.
}

class Dad
{
    Long id;
    Grandpa grandpa;
}

class Children
{
    Long id;
    Dad dad;

    Double moneyAmount;
    String moneyUni;
}

I want to write a hql to get the Grandpa objects with sum(grandpa.dad.children.money_amount) and first(grandpa.dad.children.money_unit). Here is mine but it doesn't work:

select gp, sum(chdn.moneyAmount) as gp.moneyAmount, first(chdn.moneyUnit) as gp.moneyUnit
from Grandpa gp, Dad dad, Children chdn
where gp.id =: chdn.dad.grandpa.id

Upvotes: 0

Views: 2421

Answers (1)

Firo
Firo

Reputation: 30813

select gp.id, gp.name, sum(chdn.moneyAmount) as moneyAmount, min(chdn.moneyUnit) as gp.moneyUnit
from Children chdn, chdn.dad dad, dad.grandpa gp
groupBy gp.Id

and using a aliastobean transformer to project into the grandpa dto

Or you implement moneyAmount and unit as properties which access a children collection containing the dads and sum the amounts in memory then it would be

select gp from Grandpa gp join fetch gp.children dad join fetch dad.children

but beware of gigantic cartesian products

Upvotes: 1

Related Questions