Hibernate retrieve part of collection


#1

Hi, I’m using Hibernate 5.2.12 and I’m trying to read a part of a collection.

I have a table Employee related to the table Punch.
The table Punch has a field named adjustedIn that is a long representing an epoch timestamp (in millisecond) and I would like to retrieve all Employees but only the punches between 2 epoch timestamps for the field adjustedIn.

Here is some visual data and what I expect from the query : https://pastebin.com/P4LNSM1e


#2

You can either use a LEFT JOIN;

select e
from Employee e
left join Punch p on p.adjustedIn >= :fromEpoch and p.adjustedIn < :toEpoch

If you have a collection, you ca also use a @Filter:

@OneToMany(mappedBy = "employee")
@Filter(
	name="latestPunches", 
	condition="adjustedIn >= :fromEpoch and adjustedIn < :toEpoch"
)
private List<Punch> latestPunches = new ArrayList<>( );

which you can activate it like this:

entityManager
.unwrap( Session.class )
.enableFilter( "latestPunches" )
.setParameter( "fromEpoch ", fromEpoch )
.setParameter( "toEpoch ", toEpoch );

#3

Hi @vlad and thank you for your answer! The @Filter works perfectly but I have a new problem. I have to use 3 different filters for 3 tables and 2 of them has a field “date” and it’s saying "Ambiguous column name “DATE” which makes sense but how can I change the column name within the query? I’m able to change the field name within the database but I wonder if there is another possibility!
Thank you vlad, you’re the best!


#4

I have to use 3 different filters for 3 tables and 2 of them has a field “date” and it’s saying "Ambiguous column name “DATE” which makes sense but how can I change the column name within the query?

As explained in the @Filter with @SqlFragmentAlias chapter, you need to use an alias for the DATE column that is found in multiple tables:

@Filter(
    name="activeAccount",
    condition="{a}.active = :active and {ad}.deleted = false",
    aliases = {
        @SqlFragmentAlias( alias = "a", table= "account"),
        @SqlFragmentAlias( alias = "ad", table= "account_details"),
    }
)

#5

Thank you vlad, it works perfectly!


#6

I’m glad to hear that.