I’m trying to define a custom join condition between the two following entities.
class Period {
private long id;
private String category;
private Date start;
private Date end;
@ManyToMany // Or @OneToMany
private Collection<Event> events
}
class Event {
private long id;
private String category;
private Date date;
}
I want to retrieve in Period
all the Event
that have the same category
and a date
within start
and end
(In may case, the relationship is effectively a @OneToMany
as no two periods with the same category overlap, but any working solution with @OneToMany
or @ManyToMany
would satisfy me). In sql, the join is simple:
select *
from period p
join event e on p.category = e.category
and e.date >= p.start
and e.date <= p.end;
I’ve tried multiple things, including @Formulas
and @JoinColumnsOrFormulas
and weird @JoinColumns
in @ManyToMany
trying to use the event
table as a join column, but nothing works or I didn’t find the good syntax.
With @JoinColumnsOrFormulas
, I can retrieve one Event
, but I want a collection of Event
, not a single one.
If the date
condition is impossible, even retrieving all the Event
with the same category
would be acceptable in my case, but even that seems impossible.
Note on similar question
I’m aware of this question, which concerns a @ManyToOne
mapping retrieving a single child entity.
You need to sign in to view this answers
Leave feedback about this