Using JPA isMember with ManyToMany relationship

Hibernate ORM
1

First, here are my classes:

@Entity
@Table(name = "occurrence")
public class Occurrence {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    @NotNull
    @ManyToOne
    @JoinColumn(name = "position_id")
    private Position position;

    @JsonManagedReference
    @OneToMany(mappedBy = "occurrence")
    private List<OcurrenceCompanion> companions;

}

@Entity
@Table(name = "occurrence_companion")
public class OccurrenceCompanion {

    @EmbeddedId
    private OccurrenceCompanionId id;
    
    @JsonBackReference
    @ManyToOne
    @MapsId("occurrenceId")
    private Ocurrence occurence;
    
    @ManyToOne
    @MapsId("userId")
    private User user;
    
    @ManyToOne
    @JoinColumn(name = "position_id")
    private Position position;
}

There is a ManyToMany relationship between the classes. It was necessary to create a class for OccurrenceCompanion because I needed to add an extra column to the relationship, which is the Position column. Basically it works like this: for each occurrence, there is a user who registers it (user column of the Occurrence class) and others who accompany him (companions). For each companion, I need to register his position, as well as the position of the user who registered it (Position column of the Occurrence class).

Knowing this, I need to perform a query in the database through JPA, in which it is possible to return the occurrences where a given user is the registering user or is one of the companions.

I thought about using isMember, but I came across the following situation:

builder.or(builder.equal(root.get(Occurrence_.user), occurrenceFilter.getUser()),
builder.isMember(occurrenceFilter.getUser(), root.get(Occurrence_.companions)))

OccurrenceFilter.getUser() matches the user selected in the search from the front-end.

The problem is that the isMember function is not possible, because I am comparing an object of the User type with a collection of objects of the OcurrenceCompanion type, where user is one of the attributes of each object in this collection, as shown in the OcurrenceCompanion class.

In view of this, does anyone have any tips that can help me? I would be very grateful.

2

This is a classic case for a semi join in SQL for which one usually uses the exists predicate.

Subquery<Integer> subquery = query.subquery(Integer.class);
subquery.select(builder.literal(1));
Join<?, OccurrenceCompanion> join = subquery.correlate(root).join(Occurrence_.companions);
subquery.where(builder.equal(
  join.get(OccurrenceCompanion_.user), 
  occurrenceFilter.getUser()
);
builder.or(
  builder.equal(root.get(Occurrence_.user), occurrenceFilter.getUser()),
  builder.exists(subquery)
);
3

It worked very well! Thank you very much!