Can nested query in JPQL access outer query

Question

So I'm curious if a nested SELECT can reference it's outer SELECT in order to compare values. I haven't been able to test or see many examples on this topic.

As an example, I'm trying to write a query to select all Clothes rows that has a tag (some number) that is within a given list and has the highest time that is prior to given time (which is total number of seconds). The query in question is below:

SELECT c FROM Clothes c WHERE c.tag IN :tagList 
AND (c.timeOfSale = (SELECT MAX(n.timeOfSale) FROM Clothes k 
WHERE (c.tag = k.tag) AND (k.timeOfSale) < (:time))) GROUP BY c.tag

Is the comparison c.tag = k.tag valid? If not, is there an alternative?

what does your JPA provider say when you do it?

Neil Stockton
– Neil Stockton

2016-12-21 07:29:51 +00:00
Commented Dec 21, 2016 at 7:29 — Neil Stockton
– Neil Stockton, Commented Dec 21, 2016 at 7:29

Abdullah Mohammad Motiullah · Accepted Answer · 2016-12-21 04:33:07Z

1

@Query("SELECT b FROM Business b WHERE b <> :currentBusiness "
        + "and exists "
        + "(Select i from InterestMaster i, BusinessInterest bI where bI.interestMaster = i and bI.business = b"
        + "and i in (:userInterests))")
Page<Business> getCommunityBusiness(@Param("currentBusiness") Business currentBusiness, @Param("userInterests") List<InterestMaster> userInterests,Pageable pageable);

I am using the above JPQL and its working fine. So yes nested query can access outer query.

answered Dec 21, 2016 at 4:33

Abdullah Mohammad Motiullah

12.7k6 gold badges73 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurwinder Singh · Accepted Answer · 2016-12-21 04:30:02Z

0

Yes. They're called correlated queries, where the subquery is evaluated for each row of outer query.

answered Dec 21, 2016 at 4:30

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Collectives™ on Stack Overflow

Can nested query in JPQL access outer query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related