Custom join in a @ManyToMany or @OneToMany mapping without join table

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