Tuple comparison with subquery

Hibernate ORM
1

On trying to have a single query for fetch, with pagination, the following questions arose:

  1. Is it possible to have subquery with tuple?
where (id, join_date) in (
    select distinct id, join_date from ...)
  1. Is it possible select distinct of a part of the attributes, not distinct for the whole query.
  2. Is it possible to have “order by 1“

The full query is something like:

select *
from users u1
    join files f1 on u1.id = f1.user_id
where (u1.id, u1.join_date) in (
    select distinct u2.id, u2.join_date
    from users u2
    join files f2 on u2.id = f2.user_id
    where f2.mime_type = 'pdf'
    order by u2.join_date, 1 offset 0 limit 5
)
order by u1.join_date, u1.id;

If this makes sense, feature requests could be created.

Thanks.

1 Like
2

Hello @asashour, what version of Hibernate are you using? As of e.g. 6.6:

  1. Yes, tuple comparison and in-subquery predicates are supported
  2. The HQL distinct clause works like the standard SQL one, if you apply it on a select with multiple attributes it will select distinct combinations of those attributes; I don’t really see how we could apply distinct to only one of the attributes, apart from in-memory deduplication, which would be very inefficient.
  3. Yes