Modèle

J'ai le modèle suivant avec trois classes. La classe A contient 1 B et peut contenir 0 ou plus de C. Les deux classes B et C contiennent des montants que je veux résumer ensemble dans le cadre de A.

class TableA {
    @Id
    Id id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "table_b_id", nullable = false)
    TableB tableB;

    @OneToMany(mappedBy = "tableA", fetch = FetchType.LAZY)
    Set<TableC> tableCs;
}

class TableB {
    @Id
    Id id;

    @Column(name = "amount_b")
    Long amountB;
}

class TableC {
    @Id
    Id id;

    @Column(name = "amount_c", nullable = false)
    Long amountC;

    @JoinColumn(name = "table_a_id")
    TableA tableA;
}

Question

Je suis chargé de calculer la composition de deux valeurs "somme (c.amount) + b.amount" et de voir si elles sont comprises dans un intervalle min / max défini, par ex. [0, 100]. Le résultat est une liste de A qui satisfont à cette condition. On me demande de le faire dans Hibernate Criteria uniquement.

Pour cela, j'ai construit une instruction SQL qui exprime cette exigence:

select compositeSum from 
(SELECT sum(tableC.amountC) + tableB.amountB as 'compositeSum'
FROM tableA A
left join tableB B on A.b_id = B.id
left join tableC C on C.a_id = A.id) SUBQUERY
where compositeSum between 0 and 100;

Tentative

Puisqu'il existe une fonction d'agrégation "sum ()" et aussi une opération arithmétique "+", j'ai essayé d'utiliser des sous-requêtes pour résoudre le problème. Le plus proche que j'ai obtenu était la solution suivante:

Long min = 0l;
Long max = 100l;

DetachedCriteria subquery = DetachedCriteria.forClass(TableA.class, "inner")
                .createAlias("tableC", "tableC", JoinType.LEFT_OUTER_JOIN)
                .createAlias("tableB", "tableB")
                .setProjection(Projections.sqlProjection("coalesce(sum(amountC), 0) + amountB", new String[] {"compositeSum"}, new Type[] {StandardBasicTypes.LONG}));

Criteria criteria = session().createCriteria(TableA.class, "outer")
                .add(Subqueries.ge(max, subquery))
                .add(Subqueries.le(min, subquery));

Problème

Le problème est que cela se traduit par une sous-requête utilisée dans la clause where.

select * from A this_ 
where ? >= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join table_B tableB2_ on tableA_.table_b_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id) 
and ? <= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join tableB tableB2_ on tableA_.table_B_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id)

Je voulais créer une sous-requête pouvant être alimentée dans la clause from de la requête principale. Au lieu de cela, j'ai reçu une sous-requête qui est transmise à la clause where. Ce n'est pas ce que je voulais et cela donne des résultats étranges.

Est-ce que quelqu'un sait s'il est possible d'alimenter une sous-requête vers une clause from dans Hibernate Criteria? Merci beaucoup!

Solution

(mise à jour)

Apparemment, l'ajout d'un filtre supplémentaire à l'intérieur de la requête interne qui pointe vers la requête externe a résolu le problème. Pour faire référence à l'entité de requête parente de la sous-requête, utilisez le mot clé magique "this".

Long min = 0l;
Long max = 100l;

DetachedCriteria subquery = DetachedCriteria.forClass(TableA.class, "inner")
                .createAlias("tableC", "tableC", JoinType.LEFT_OUTER_JOIN)
                .createAlias("tableB", "tableB")
                .setProjection(Projections.sqlProjection("coalesce(sum(amountC), 0) + amountB", new String[] {"compositeSum"}, new Type[] {StandardBasicTypes.LONG}))
                .add(Restrictions.eqProperty("id", "this.id"));

Criteria criteria = session().createCriteria(TableA.class, "outer")
                .setProjection(Projections.property("id"))
                .add(Subqueries.ge(max, subquery))
                .add(Subqueries.le(min, subquery));

La logique derrière l'ajout d'un filtre supplémentaire est que le min / max est un scalaire, et vous voulez que la sous-requête renvoie un autre scalaire, et non une liste, à comparer, ce qui entraînerait autrement un comportement erratique.

Cela devient alors une sous-requête corrélée:

select * from A this_ 
where ? >= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join table_B tableB2_ on tableA_.table_b_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id 
where tableA_.id = this_.id) 
and ? <= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join tableB tableB2_ on tableA_.table_B_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id
where tableA_.id = this_.id)

Cette solution est un type de sous-requête différent, et est probablement un peu plus lente puisque vous exécutez la sous-requête deux fois, mais elle fait le travail.

Clause-avoir

Notez qu'une solution SQL serait également possible en utilisant une clause ayant. Ci-dessous, vous pouvez trouver la conversion de ce qui précède en une clause ayant. Le seul problème est que Hibernate Criteria ne prend pas en charge les clauses-avoir.

SELECT coalesce(sum(amountC),0) + amountB as 'compositeSum'
FROM table_A tableA
left join table_B tableB on tableA.table_b_id = tableB.id
left join table_C tableC on tableC.table_a_id = tableA.id

group by m.id
having compositeSum between 0 and 100
1
CCC 17 janv. 2017 à 12:08

2 réponses

Meilleure réponse

Apparemment, l'ajout d'un filtre supplémentaire à l'intérieur de la requête interne qui pointe vers la requête externe a résolu le problème. Pour faire référence à l'entité de requête parente de la sous-requête, utilisez le mot clé magique "this".

Long min = 0l;
Long max = 100l;

DetachedCriteria subquery = DetachedCriteria.forClass(TableA.class, "inner")
                .createAlias("tableC", "tableC", JoinType.LEFT_OUTER_JOIN)
                .createAlias("tableB", "tableB")
                .setProjection(Projections.sqlProjection("coalesce(sum(amountC), 0) + amountB", new String[] {"compositeSum"}, new Type[] {StandardBasicTypes.LONG}))
                .add(Restrictions.eqProperty("id", "this.id"));

Criteria criteria = session().createCriteria(TableA.class, "outer")
                .setProjection(Projections.property("id"))
                .add(Subqueries.ge(max, subquery))
                .add(Subqueries.le(min, subquery));

La logique derrière l'ajout d'un filtre supplémentaire est que le min / max est un scalaire, et vous voulez que la sous-requête renvoie un autre scalaire, et non une liste, à comparer, ce qui entraînerait autrement un comportement erratique.

Cela devient alors une sous-requête corrélée:

select * from A this_ 
where ? >= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join table_B tableB2_ on tableA_.table_b_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id 
where tableA_.id = this_.id) 
and ? <= 
(select sum(amountC) + amountB from table_A tableA_ 
inner join tableB tableB2_ on tableA_.table_B_id=tableB2_.id 
inner join table_C tableC1_ on tableA_.id=tableC1_.table_a_id
where tableA_.id = this_.id)

Clause-avoir

Notez qu'une solution SQL serait également possible en utilisant une clause ayant. Ci-dessous, vous pouvez trouver la conversion de ce qui précède en une clause ayant. Le seul problème est que Hibernate Criteria ne prend pas en charge les clauses-avoir.

SELECT coalesce(sum(amountC),0) + amountB as 'compositeSum'
FROM table_A tableA
left join table_B tableB on tableA.table_b_id = tableB.id
left join table_C tableC on tableC.table_a_id = tableA.id

group by m.id
having compositeSum between 0 and 100
0
CCC 18 janv. 2017 à 07:57

Vous n'avez pas besoin de sous-requête. Simplifiez votre requête comme ci-dessous:

SELECT sum(tableC.amountC) + tableB.amountB as 'compositeSum'
FROM tableA A
left join tableB B on A.b_id = B.id
left join tableC C on C.a_id = A.id
where (sum(tableC.amountC) + tableB.amountB) between 0 and 100;

Et maintenant changez votre code de mise en veille prolongée en conséquence.

0
Ninad Shaha 17 janv. 2017 à 09:33