0

I want to display the names which are stored in the customer's profiles when showing the customer's orders. I have tried to write the query in pgAdmin and I show the data as I want.

Select O, S.id as sid, PR.title, C.email, P.name
From orders O
inner join subscription S on o.subscription_id = S.id
inner join product PR on S.product_id = PR.id 
inner join account C on S.account_id = C.id
inner join profile P on (P.user_id = C.id)
where PR.merchant_id = :merchantId;

The problem is that I am not able to translate the above query to JPA criteria because of subquery on P.user_id = C.id. I have tried to find a similar question in the forum or google but I can't find a good example on this case. This is the current translation I have. 

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<CusODTO> query = builder.createQuery(CusODTO.class);
Root<Order> rootOrder = query.from(Order.class);
Join<Order, Subscription> joinSub = rootOrder.join(Order_.subscription);
Join<Subscription, Product> joinPro = joinSub.join(Subscription_.product);
Join<Subscription, Customer> joinCus = joinSub.join(Subscription_.customer);

// TODO implement subquery to get joinCustomer profile

query.select(builder.construc(
    CusODTO.class
    , rootOrder
    , joinSub.get(Subscription_.id)
    , joinPro.get(Product_.title))
    , joinCus.get(Customer_.email)
    , "nullName"             // should select joinCustomer profile name
    )
).where(buider.equal(joinPro.get(Product_.merchant), merchant))

Can you help to complete it?

Thanks in advance

Long Hoang
  • 1
  • 1
  • Possible duplicate of [Hibernate HQL Subquery](http://stackoverflow.com/questions/30382086/hibernate-hql-subquery) – Scott Sosna May 02 '16 at 01:05
  • @ScottSosna why do you indicate a completely different question as duplicate? Have you read it? – perissf May 02 '16 at 08:29
  • Long Hoang, why do you think you have a subquery? Have you seen some examples of JPA CriteriaBuilder subqueries? More importantly, you are misusing CriteriaBuilder.construct, that method has a completely different purpose. Have you read the javadocs and a JPA criteria builder basic tutorial? – perissf May 02 '16 at 08:31

0 Answers0