java - 具有多个表的HQL联接和sum()，first()

塔布斯：

Grandpa
    GP_ID  (PK)
    NAME

Dad
    DAD_ID (PK)
    PG_ID  (FK)

Children
    C_ID   (PK)
    DAD_ID (FK)
    MONEY_AMOUNT
    MONEY_UNIT

类（此处省略注释）：

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;
}

我想编写一个hql以获取具有sum（grandpa.dad.children.money_amount）和first（grandpa.dad.children.money_unit）的爷爷对象。这是我的，但不起作用：

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

最佳答案

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

并使用aliastobean变压器将其投影到爷爷dto中

或者，您将moneyAmount和unit实现为属性，该属性访问包含父亲的子级集合并将其总和存储在内存中，

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

但要提防巨大的笛卡尔积