spring data jpa unnecessary left join

Question

I have the following model:

I want to get all Institutions (Intituciones) with specified sectorId.

In the tbInstitucion model I have a relationship with tbSector:

 @ManyToOne(fetch=FetchType.LAZY)
 @JoinColumn(name="`sectorId`")
 private Sector sector;

is there a way to obtain a query like:

select * 
from tbInstitucion 
where sectorId = ?

I tried with: findBySector(Sector sector)

but with this I need an additional query to find the sector and findBySector is generating the following query:

select
        generatedAlias0.institucionId,
        generatedAlias0.institucionNombre 
    from
        Institucion as generatedAlias0 
    left join
        generatedAlias0.sector as generatedAlias1 
    where
        generatedAlias1=:param0

tried with this other one:

findBySector_sectorId

which generates the above query as well.

Wouldn't be better to form a query like:

select * 
from tbInstitucion 
where sectorId = ?

Is there a way to get the above query? Why is JPA generating the left join?

Answer 1

A quick review of the entity model

@Entity
class Institucion {
  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="`sectorId`")
  private Sector sector;
}

is equivalent to:

@Entity
class Institucion {
  @ManyToOne(cascade = {}
             , fetch=FetchType.LAZY
             , optional = true
             , targetEntity = void.class)
  @JoinColumn(columnDefinition = ""
             , foreignKey = @ForeignKey
             , insertable = true
             , name="`sectorId`"
             , nullable = true
             , referencedColumnName = ""
             , table = ""
             , unique = false
             , updatable = false)
  private Sector sector;
}

Note @ManyToOne(optional = true) and @JoinColumn(nullable = true). This signifies to the ORM that the sector attribute of Institucion is optional and may not be set (to a non-null value) all the time.

---

How the entity model impacts repository queries

Now consider the following repository:

public interface InstitucionRepository extends CrudRepository<Institucion, Long> {
  List<Institucion> findAllByInstitucionNombre(String nombre);

  List<Institucion> findAllByInstitucionEmail(String email);
}

Given the entity declaration above, the repository methods should produce queries such as:

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias0.institucionNombre=:param0

and

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias0.institucionEmail=:param0

This is because the entity model indicates sector to be optional so the ORM needs to load Institucions without worrying about their sectors.

Following this pattern, the following repository method:

  List<Institucion> findAllBySector(Sector sector);

translates to:

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias1=:param0

---

Solution 1

If Institucion.sector is not optional, make it mandatory in the model too:

  @ManyToOne(fetch=FetchType.LAZY, optional = false)
  @JoinColumn(name="`sectorId`", nullable = false)
  private Sector sector;

Solution 2

If Institucion.sector is indeed optional, only a manual query such as the one shown in @MaciejKowalski's answer will work.

---

Simplified query

The following query will also work:

  List<Institucion> findAllBySectorSectorId(Long id);

This assumes that the model attribute names are exactly as shown in the post.

Answer 2

Left join is a default implicit joining strategy, also when using the @EntityGraph feature.

I would recommend using explicit @Query definition:

@Query("select i from institution i inner join i.sector s where s.id = :sectorId")
public Institution getBySector(@Param("sectorId") Integer sectorId);