CODEWITHSUNDEEP
javasqljpa
Koray Tugay
Koray Tugay

Reputation: 23800

How to find intersection of many to many entities directly by JQL?

These are my model classes:

Film.java

@Entity
public class Film {

    private Integer id;
    private String title;
    private List<FilmActor> filmActors;

    @OneToMany(mappedBy = "film")
    public List<FilmActor> getFilmActors() {
        return filmActors;
    }

    public void setFilmActors(List<FilmActor> filmActors) {
        this.filmActors = filmActors;
    }
}

Actor.java

@Entity
public class Actor {

    private Integer id;
    private String firstname;
    private String lastname;

    private List<FilmActor> filmActors;

    @OneToMany(mappedBy = "actor")
    public List<FilmActor> getFilmActors() {
        return filmActors;
    }

    public void setFilmActors(List<FilmActor> filmActors) {
        this.filmActors = filmActors;
    }
}

And this is the Join Table Entity:

@Entity
@Table(name = "film_actor")
public class FilmActor {

    private FilmActorPK id;

    private Film film;
    private Actor actor;
    private Timestamp lastUpdate;

    @EmbeddedId
    public FilmActorPK getId() {
        return id;
    }

    public void setId(FilmActorPK id) {
        this.id = id;
    }

    @ManyToOne
    @MapsId("film")
    @JoinColumn(name = "film_id")
    public Film getFilm() {
        return film;
    }

    public void setFilm(Film film) {
        this.film = film;
    }

    @ManyToOne
    @MapsId("actor")
    @JoinColumn(name = "actor_id")
    public Actor getActor() {
        return actor;
    }

    public void setActor(Actor actor) {
        this.actor = actor;
    }

    @Column(name = "last_update")
    public Timestamp getLastUpdate() {
        return lastUpdate;
    }

    public void setLastUpdate(Timestamp lastUpdate) {
        this.lastUpdate = lastUpdate;
    }
}

and the Primary Key class:

@Embeddable
public class FilmActorPK implements Serializable {

    private int actorId;
    private int filmId;

    @Column(name = "actor_id")
    public int getActorId() {
        return actorId;
    }

    public void setActorId(int actorId) {
        this.actorId = actorId;
    }

    @Column(name = "film_id")
    public int getFilmId() {
        return filmId;
    }

    public void setFilmId(int filmId) {
        this.filmId = filmId;
    }
}

So I want to find films where 2 given actors acts. This is what I have:

@Override
public Collection<Film> filmsActorsTogether(Actor a, Actor b) {
    final List<Film> filmsOfActorA = filmsOfActor(a);
    final List<Film> filmsOfActorB = filmsOfActor(b);
    final Collection<Film> intersection = CollectionUtils.intersection(filmsOfActorA, filmsOfActorB);
    return intersection;
}

@Override
public List<Film> filmsOfActor(Actor actor) {
    final EntityManager entityManager = persistenceUtil.getEntityManager();
    final Actor persistentActor = entityManager.find(Actor.class, actor.getId());
    final ArrayList<Film> films = new ArrayList<Film>();
    for (FilmActor filmActor : persistentActor.getFilmActors()) {
        films.add(filmActor.getFilm());
    }
    entityManager.close();
    return films;
}

Is there any way to achieve this without fetching ALL films of 2 actors, and using filtering in memory? How do I get the Films directly from the DB with JQL?

Upvotes: 0

Views: 140

Answers (1)

JB Nizet
JB Nizet

Reputation: 692003

Maybe there is something more elegant, but the following query should work:

select f from Film f where 
   (select count(fa.id) from FilmActor fa 
           where fa.film = f 
             and (fa.actor = :actor1 or fa.actor = :actor2)) = 2

Side note: your PK class should have a correct equals() and hashCode() methods

Upvotes: 1

Related Questions