CODEWITHSUNDEEP
hibernatejpaspring-data-jpaquerydsl
Ploo
Ploo

Reputation: 537

QueryDSL generates cross join

I am looking to offer content filtering for results. My (edited for brevity) entities look like following:

Node:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class Node {
    @Id
    @GeneratedValue
    public Integer id;
}

Scene:

@Entity
public class Scene extends Node {
    @OneToMany(mappedBy = "scene", /*fetch = FetchType.EAGER, */cascade = CascadeType.ALL)
    public List<Source> sources;
}

Source:

@Entity
public class Source extends Node {
    @ManyToOne
    public Scene scene;

    @Enumerated(EnumType.ORDINAL)
    public SourceType type;
}

What follows is an example of a filter I wish to implement.

Given a collection of SourceTypes I wish to select all Scenes such that this scene is referred by a source of each one of those types. I achieve this using QueryDSL with the following predicate:

private Predicate filterBySourceType(Collection<SourceType> it) {
    BooleanBuilder bb = new BooleanBuilder();

    for (SourceType st : it)
        bb.and(QScene.scene.sources.any().type.eq(st));

    return bb.getValue();
}

A series of these predicates are combined to give an overall query. When selecting even just one SourceType the resulting query looks like so:

Hibernate: select count(scene0_.id) as col_0_0_ from Scene scene0_ inner join Node scene0_1_ on scene0_.id=scene0_1_.id where exists (select 1 from Source source1_ inner join Node source1_1_ on source1_.id=source1_1_.id where (source1_.id in (select sources2_.id from Source sources2_ inner join Node sources2_1_ on sources2_.id=sources2_1_.id where scene0_.id=sources2_.scene_id)) and source1_.type=?)

I believe what happens above is a cross join and as a result (2k scenes, 1k sources) the query takes multiple seconds.

I tried switching to concrete class polymorphism as to eliminate the Node joins with no noticeable performance improvement.

How could I optimise that predicate?

Upvotes: 3

Views: 2364

Answers (1)

Ploo
Ploo

Reputation: 537

Turns out you can use JPASubQuery to get a CollectionExpression and write out the query similarly to how you would write it with plain old SQL.

        bb.and(QScene.scene.in(
                new JPASubQuery()
                        .from(source)
                        .where(source.type.eq(st))
                        .list(source.scene)
        ));

Upvotes: 3

Related Questions