1

I can define multiple phone numbers for a contact. Phones are a sorted collection, each phone has a preferrence (int pref) which allows to select primary (preferred) contact with a subquery in a select clause:

select c.first_name, c.last_name, c.birth_date,
(select ph.value from phone ph where ph.contact_id = c.id order by ph.pref limit 1)
from contact c

How can I achieve the same with JPQL, without using the native queries? The LIMIT keyword is not supported by JPQL.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Danubian Sailor
  • 1
  • 38
  • 145
  • 223
  • 1
    As the word "primary" would suggest, there should only ever be 1 phone, which can be preferred. Thus you should be able to add a `and ph.pref = 1` to the where clause of the sub select. – XtremeBaumer May 16 '22 at 08:03
  • @XtremeBaumer it's a 'trick' that would work if the business layer would be disciplined enough to enforce that. It's actually the simplest case, I have cases when I want to get the most recently changed subvalue. – Danubian Sailor May 16 '22 at 08:23
  • `I have cases when I want to get the most recently changed subvalue` for that case, you would need a separate query anway. And in that case, you just need another subselect, to get the `max(updateDateTime)`. There is rarely, if ever, a need for `LIMIT` as everything can be done with proper subselects. And why not use native queries? Does your database change constantly? – XtremeBaumer May 16 '22 at 08:27
  • @XtremeBaumer it should be portable, if the database was fixed, I wouldn't need JPA layer – Danubian Sailor May 16 '22 at 09:50
  • https://stackoverflow.com/questions/8555485/how-to-impose-limit-on-sub-query-of-jpa-query https://stackoverflow.com/questions/37187193/criteria-api-limit-results-in-subquery -> basically you will need to go with [`CriteriaQuery`](https://www.baeldung.com/hibernate-criteria-queries) then – XtremeBaumer May 16 '22 at 09:57

0 Answers0