CODEWITHSUNDEEP
springjpaspring-data-jpaquerydsl
codependent
codependent

Reputation: 24452

Spring Data JPA + QueryDSL Query optimization

I have come across a weird behaviour when using QueryDSL integrated with Spring Data JPA:

I have a ManyToOne relation between Project and Person. If I get all the projects belonging to a user by owner id (foreign key) everything works as expected:

QProject project = QProject.project;
QPerson owner = project.owner;
List<Project> projects = from(project).leftJoin(owner).fetch()
    .where(owner.id.eq(id)).list(project);

Generated query:

select
    project0_.id as id1_1_0_,
    person1_.id as id1_0_1_,
    project0_.creation_date as creation2_1_0_,
    project0_.name as name3_1_0_,
    project0_.owner as owner4_1_0_,
    person1_.name as name2_0_1_
from
    project project0_
left outer join
    person person1_
        on project0_.owner=person1_.id
where
    project0_.owner=?

However, let's say we want to get all the projects belonging to a person by a field that isn't the foreign key (for instance the owner's name):

QProject project = QProject.project;
QPerson owner = project.owner; 
List<Project> projects = from(project).leftJoin(owner).fetch()
    .where(owner.name.eq(name)).list(project);

In these cases, the table Person is joined twice unnecessarily (notice person1_ and person2_):

select
    project0_.id as id1_1_0_,
    person1_.id as id1_0_1_,
    project0_.creation_date as creation2_1_0_,
    project0_.name as name3_1_0_,
    project0_.owner as owner4_1_0_,
    person1_.name as name2_0_1_
from
    project project0_
left outer join
    person person1_
        on project0_.owner=person1_.id cross
join
    person person2_
where
    project0_.owner=person2_.id
    and person2_.name=?

Any idea why this is happening and how to avoid it?

Upvotes: 1

Views: 1887

Answers (1)

Timo Westk&#228;mper
Timo Westk&#228;mper

Reputation: 22190

You need to create an alias to ensure that the first join is reused in the where part

QProject project = QProject.project;
QPerson owner = new QPerson("owner");
List<Project> projects = from(project)
    .leftJoin(project.owner, owner).fetch()
    .where(owner.name.eq(name))
    .list(project);

Upvotes: 1

Related Questions