问题

以下是你要翻译的内容：

Working SQL:

SELECT *
FROM   tag
       JOIN (SELECT tag_id,
                    Count (gift_certificate_tag.gift_certificate_id) AS
                    cert_count
             FROM   gift_certificate_tag
                    JOIN orders
                      ON gift_certificate_tag.gift_certificate_id =
                         orders.gift_certificate_id
                    JOIN (SELECT user_id,
                                 Sum (order_cost) AS all_orders_cost
                          FROM   orders
                          GROUP  BY user_id
                          ORDER  BY all_orders_cost DESC
                          LIMIT  1) AS orders_sum
                      ON orders_sum.user_id = orders.user_id
             GROUP  BY tag_id
             ORDER  BY cert_count DESC
             LIMIT  1) AS result
             ON result.tag_id = tag.id

Working JPQL:

SELECT new Tag(t.id, t.name)
            FROM GiftCertificate gc
            JOIN gc.tags t
            JOIN Order o ON o.giftCertificate = gc
            JOIN o.user u
            WHERE u = (
                  SELECT ou
                  FROM Order o2
                  JOIN o2.user ou
                  GROUP BY ou
                  HAVING SUM(o2.orderCost) = (
                        SELECT MAX(totalOrderCost)
                        FROM (
                              SELECT SUM(o3.orderCost) AS totalOrderCost
                              FROM Order o3
                              GROUP BY o3.user
                        ) subquery
                  )
            )
            GROUP BY t.id, t.name
            ORDER BY COUNT(gc) DESC

Not working Criteria Query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
Root<Order> order = cq.from(Order.class);
Join<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
Join<GiftCertificate, Tag> tag = giftCertificate.join("tags");
Join<Order, User> user = order.join("user");

//sub-query to find user's orders sums
Subquery<BigDecimal> querySumOfOrders = cq.subquery(BigDecimal.class);
Root<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
Join<Order, User> subUserSumOfOrders = subOrderSumOfOrders.join("user");
querySumOfOrders.select(cb.sum(subOrderSumOfOrders.get("orderCost")))
                .groupBy(subUserSumOfOrders);

//sub-query to find max cost of the user's orders sum
Subquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
queryMaxOrdersSum.from(Order.class);
queryMaxOrdersSum.select(cb.max(querySumOfOrders));

//sub-query to find user with most cost of all orders
Subquery<User> queryMostSpentUser = cq.subquery(User.class);
Root<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
Join<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
                  .having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
                  .groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
  .where(cb.equal(user, queryMostSpentUser))
  .orderBy(cb.desc(cb.count(giftCertificate)))
  .groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
                                 .setMaxResults(1)
                                 .getResultList();

The SQL generated by Hibernate from criteria query:

select
    t1_1.id,
    t1_1.name 
from
    public.orders o1_0 
join
    public.gift_certificate g1_0 
        on g1_0.id=o1_0.gift_certificate_id 
join
    (public.gift_certificate_tag t1_0 
join
    public.tag t1_1 
        on t1_1.id=t1_0.tag_id) 
            on g1_0.id=t1_0.gift_certificate_id 
    join
        public.users u1_0 
            on u1_0.id=o1_0.user_id 
    where
        u1_0.id=(
            select
                u2_0.id 
            from
                public.orders o2_0 
            join
                public.users u2_0 
                    on u2_0.id=o2_0.user_id 
            group by
                1 
            having
                sum(o2_0.order_cost)=(
                    select
                        max((select
                            sum(o4_0.order_cost) 
                        from
                            public.orders o4_0 
                        join
                            public.users u3_0 
                                on u3_0.id=o4_0.user_id 
                        group by
                            o4_0.user_id)) 
                    from
                        public.orders o3_0)
                ) 
            group by
                1,
                2 
            order by
                count(o1_0.gift_certificate_id) desc fetch first ? rows only

The problem is with the following SQL block:

select max((select
                            sum(o4_0.order_cost) 
                        from
                            public.orders o4_0 
                        join
                            public.users u3_0 
                                on u3_0.id=o4_0.user_id 
                        group by
                            o4_0.user_id))

this causes org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression

The question is how to make criteria query instead generate SQL same as generated in case of JPQL query:

                    select max(subquery1_0.totalOrderCost) 
                    from
                        (select
                            sum(o3_0.order_cost) 
                        from
                            public.orders o3_0 
                        group by
                            o3_0.user_id) subquery1_0(totalOrderCost)

@Entity
public class GiftCertificate {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String description;
@Column(precision = 10, scale = 2)
private BigDecimal price;
private Short duration;
private LocalDateTime createDate;
private LocalDateTime lastUpdateDate;
@ManyToMany
@JoinTable(
name = "gift_certificate_tag",
joinColumns = @JoinColumn(name = "gift_certificate_id"),
inverseJoinColumns = @JoinColumn(name = "tag_id")
)
private Set<Tag> tags = new HashSet<>();
public Set<Tag> getTags() {
return Collections.unmodifiableSet(tags);
}
}
@Entity
@Table(name = "ORDERS")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private User user;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
private GiftCertificate giftCertificate;
private LocalDateTime orderDate;
private BigDecimal orderCost;
}
@Entity
@Table(name = "tag", schema = "public")
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
//@Basic(optional = false)
//@NotNull
@Column(nullable = false)
private String name;
}
@Entity
@Table(name = "users", schema = "public")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String userName;
}

SELECT *
FROM   tag
JOIN (SELECT tag_id,
Count (gift_certificate_tag.gift_certificate_id) AS
cert_count
FROM   gift_certificate_tag
JOIN orders
ON gift_certificate_tag.gift_certificate_id =
orders.gift_certificate_id
JOIN (SELECT user_id,
Sum (order_cost) AS all_orders_cost
FROM   orders
GROUP  BY user_id
ORDER  BY all_orders_cost DESC
LIMIT  1) AS orders_sum
ON orders_sum.user_id = orders.user_id
GROUP  BY tag_id
ORDER  BY cert_count DESC
LIMIT  1) AS result
ON result.tag_id = tag.id

SELECT new Tag(t.id, t.name)
FROM GiftCertificate gc
JOIN gc.tags t
JOIN Order o ON o.giftCertificate = gc
JOIN o.user u
WHERE u = (
SELECT ou
FROM Order o2
JOIN o2.user ou
GROUP BY ou
HAVING SUM(o2.orderCost) = (
SELECT MAX(totalOrderCost)
FROM (
SELECT SUM(o3.orderCost) AS totalOrderCost
FROM Order o3
GROUP BY o3.user
) subquery
)
)
GROUP BY t.id, t.name
ORDER BY COUNT(gc) DESC

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
Root<Order> order = cq.from(Order.class);
Join<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
Join<GiftCertificate, Tag> tag = giftCertificate.join("tags");
Join<Order, User> user = order.join("user");
//sub-query to find user's orders sums
Subquery<BigDecimal> querySumOfOrders = cq.subquery(BigDecimal.class);
Root<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
Join<Order, User> subUserSumOfOrders = subOrderSumOfOrders.join("user");
querySumOfOrders.select(cb.sum(subOrderSumOfOrders.get("orderCost")))
.groupBy(subUserSumOfOrders);
//sub-query to find max cost of the user's orders sum
Subquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
queryMaxOrdersSum.from(Order.class);
queryMaxOrdersSum.select(cb.max(querySumOfOrders));
//sub-query to find user with most cost of all orders
Subquery<User> queryMostSpentUser = cq.subquery(User.class);
Root<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
Join<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();

select
t1_1.id,
t1_1.name 
from
public.orders o1_0 
join
public.gift_certificate g1_0 
on g1_0.id=o1_0.gift_certificate_id 
join
(public.gift_certificate_tag t1_0 
join
public.tag t1_1 
on t1_1.id=t1_0.tag_id) 
on g1_0.id=t1_0.gift_certificate_id 
join
public.users u1_0 
on u1_0.id=o1_0.user_id 
where
u1_0.id=(
select
u2_0.id 
from
public.orders o2_0 
join
public.users u2_0 
on u2_0.id=o2_0.user_id 
group by
1 
having
sum(o2_0.order_cost)=(
select
max((select
sum(o4_0.order_cost) 
from
public.orders o4_0 
join
public.users u3_0 
on u3_0.id=o4_0.user_id 
group by
o4_0.user_id)) 
from
public.orders o3_0)
) 
group by
1,
2 
order by
count(o1_0.gift_certificate_id) desc fetch first ? rows only

                select max((select
sum(o4_0.order_cost) 
from
public.orders o4_0 
join
public.users u3_0 
on u3_0.id=o4_0.user_id 
group by
o4_0.user_id))

                        select max(subquery1_0.totalOrderCost) 
from
(select
sum(o3_0.order_cost) 
from
public.orders o3_0 
group by
o3_0.user_id) subquery1_0(totalOrderCost)

答案1

得分: 1

以下是翻译好的代码部分：

"The "JPQL" (actually HQL) can only work with Hibernate 6+, because before that, Hibernate did not support subqueries in the from clause. To use that feature from JPA Criteria, you have to use the Hibernate JPA Criteria extensions.

HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
JpaCriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
JpaRoot<Order> order = cq.from(Order.class);
JpaJoin<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
JpaJoin<GiftCertificate, Tag> tag = giftCertificate.join("tags");
JpaJoin<Order, User> user = order.join("user");

//sub-query to find user's orders sums
JpaSubquery<Tuple> querySumOfOrders = cq.subquery(Tuple.class);
JpaRoot<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
querySumOfOrders.multiselect(cb.sum(subOrderSumOfOrders.get("orderCost")).alias("theSum"))
                .groupBy(subOrderSumOfOrders.get("user"));

//sub-query to find max cost of the user's orders sum
JpaSubquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
JpaRoot<Tuple> queryMaxOrders = queryMaxOrdersSum.from(querySumOfOrders);
queryMaxOrdersSum.select(cb.max(queryMaxOrders.get("theSum")));

//sub-query to find user with most cost of all orders
JpaSubquery<User> queryMostSpentUser = cq.subquery(User.class);
JpaRoot<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
JpaJoin<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
                  .having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
                  .groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
  .where(cb.equal(user, queryMostSpentUser))
  .orderBy(cb.desc(cb.count(giftCertificate)))
  .groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
                                 .setMaxResults(1)
                                 .getResultList();

希望这对你有所帮助。如果有任何其他问题，请随时提出。

    HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
JpaCriteriaQuery<Tag> cq = cb.createQuery(Tag.class);
JpaRoot<Order> order = cq.from(Order.class);
JpaJoin<Order, GiftCertificate> giftCertificate = order.join("giftCertificate");
JpaJoin<GiftCertificate, Tag> tag = giftCertificate.join("tags");
JpaJoin<Order, User> user = order.join("user");
//sub-query to find user's orders sums
JpaSubquery<Tuple> querySumOfOrders = cq.subquery(Tuple.class);
JpaRoot<Order> subOrderSumOfOrders = querySumOfOrders.from(Order.class);
querySumOfOrders.multiselect(cb.sum(subOrderSumOfOrders.get("orderCost")).alias("theSum"))
.groupBy(subOrderSumOfOrders.get("user"));
//sub-query to find max cost of the user's orders sum
JpaSubquery<BigDecimal> queryMaxOrdersSum = cq.subquery(BigDecimal.class);
JpaRoot<Tuple> queryMaxOrders = queryMaxOrdersSum.from(querySumOfOrders);
queryMaxOrdersSum.select(cb.max(queryMaxOrders.get("theSum")));
//sub-query to find user with most cost of all orders
JpaSubquery<User> queryMostSpentUser = cq.subquery(User.class);
JpaRoot<Order> subOrderMostSpentUser = queryMostSpentUser.from(Order.class);
JpaJoin<Order, User> subUserMostSpentUser = subOrderMostSpentUser.join("user");
queryMostSpentUser.select(subUserMostSpentUser)
.having(cb.equal(cb.sum(subOrderMostSpentUser.get("orderCost")), cb.max(queryMaxOrdersSum)))
.groupBy(subUserMostSpentUser);
//main query
cq.select(tag)
.where(cb.equal(user, queryMostSpentUser))
.orderBy(cb.desc(cb.count(giftCertificate)))
.groupBy(tag);
List<Tag> tagList = entityManager.createQuery(cq)
.setMaxResults(1)
.getResultList();