Hibernate LEFT JOIN FETCH with ON clause or alternative

SOLUTION:

I found solution for fetching and multi conditions for join operation: We can apply EntityGraph feature:

@NamedEntityGraph(name = "graph.Parent.children", attributeNodes = @NamedAttributeNode("children"))
public class Parent {
 ...
}

And in the repository, you need to link this EntityGraph with the Query like this:

...
@Query("select distinct p from Parent p left join p.children c on YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMont")
@EntityGraph(value = "graph.Parent.children")
List<Parent> findAllParents(Integer dateYear, Integer dateMont);
...

Previous solution:

As you know, Hibernate doesn't allow use more then one condition in on for fetch join, as result: with-clause not allowed on fetched associations; use filters exception, therefore I propose to use the following HQL query:

select distinct p from Parent p 
left join fetch p.children c 
where (YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMont) 
or c.parent is null

Note: distinct is required for avoiding duplicates for parent entities, about that there is article: https://thorben-janssen.com/hibernate-tips-apply-distinct-to-jpql-but-not-sql-query/

As result, Hibernate will generate one query with supporting parents without children, if for children should be order as on posted json example, you need to add @SortNatural for children field:

@OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JsonManagedReference
@MapKey(name = "date")
@SortNatural
private Map<LocalDate, Child> children;

as result, the json will be the same as posted.

As recommendation: Don't use calculating values on runtime, for these operations index cannot be applied, in your case I propose to use virtual column(if Mysql) or separate indexed column only with prepared values for searching, and try to join tables by indexes, these recommendations will speed up your query execution.

Your original query:

@Query("select p from Parent p left join p.child c on YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMonth")

looks fine, it will return all the Parent entities independently if they have or not children as you need.

The problem is that afterwards, for any reason, probably when you serialize the information to JSON format, for every parent returned by the original query, the OneToMany relationship with children is being resolved. As a consequence, the second query you mentioned is executed and you obtain as a result children that does not meet the filter criteria.

Although it is unclear to me where your transactions demarcations are established, this may happen because when the entities are being serialized they are still associated with a persistence context - on the contrary, it will cause problems with lazy collection initialization.

One option you can try is detach those entities from the persistence context before serializing them.

To achieve that goal, first, include the fetch term in your query:

@Query("select p from Parent p left join fetch p.children c on YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMonth"

Unfortunately Spring Data does not provide an out of the box solution for detaching entities, but you can easily implement the necessary code.

First, define a base repository interface:

import java.io.Serializable;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

@NoRepositoryBean
public interface CustomRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
  void detach(T entity);
}

And its implementation:

import java.io.Serializable;

import javax.persistence.EntityManager;

import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

public class CustomRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements CustomRepository<T, ID> {

  private final EntityManager entityManager;

  public CustomRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
    super(entityInformation, entityManager);
    this.entityManager = entityManager;
  }

  public CustomRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
    super(domainClass, entityManager);
    this.entityManager = entityManager;
  }

  public void detach(T entity) {
    this.entityManager.detach(entity);
  }

}

Now, create a factory bean for the new repository:

import java.io.Serializable;

import javax.persistence.EntityManager;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.data.repository.core.RepositoryMetadata;
import org.springframework.data.repository.core.support.RepositoryFactorySupport;

public class CustomRepositoryFactoryBean<R extends JpaRepository<T, I>, T, I extends Serializable> extends JpaRepositoryFactoryBean<R, T, I> {

  public CustomRepositoryFactoryBean(Class<? extends R> repositoryInterface) {
    super(repositoryInterface);
  }

  @Override
  protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
    return new CustomRepositoryFactory(entityManager);
  }

  private static class CustomRepositoryFactory<T, I extends Serializable> extends JpaRepositoryFactory {

    private final EntityManager em;

    public CustomRepositoryFactory(EntityManager em) {

      super(em);
      this.em = em;
    }

    protected <T, ID extends Serializable> SimpleJpaRepository<?, ?> getTargetRepository(RepositoryMetadata metadata, EntityManager entityManager) {
      SimpleJpaRepository<?, ?> repo = new CustomRepositoryImpl(metadata.getDomainType(), entityManager);
      return repo;
    }

    protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
      return CustomRepositoryImpl.class;
    }
  }

}

This factory must be registered in your @EnableJpaRepositories annotation:

@EnableJpaRepositories(repositoryFactoryBeanClass = CustomRepositoryFactoryBean.class /* your other configuration */)

Your repository needs to implement the new one defined:

public interface ParentRepository extends CustomRepository<Parent, Integer> {

    @Query("select p from Parent p left join fetch p.children c where (YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMonth) or c.parent is null")
    @Transactional(readOnly=true)
    public List<Parent> getParentsForDate(@Param("dateOccurred") LocalDate dateOccurred)
}

Create a new service:

public interface ParentService {
  List<Parent> getParentsForDate(LocalDate dateOccurred);
}

And the corresponding implementation:

@Service
public class ParentServiceImpl implements ParentService {
  private final ParentRepository parentRepository;

  public ParentServiceImpl(ParentRepository parentRepository) {
    this.parentRepository = parentRepository;
  }

  @Override
  @Transactional
  public List<Parent> getParentsForDate(LocalDate dateOccurred) {
    Lis<Parent> parents = parentRepository.getParentsForDate(dateOccurred);

    // Now, the tricky part... I must recognize that I never tried
    // something like this, but I think it should work
    parents.forEach(parent -> parentRepository.detach(parent));
    return parents;
  }
}

And use the service in the controller:

@GetMapping(path = "/parents/{date}", produces = { "application/json" })
@Operation(summary = "Get all parents for date")
public @ResponseBody List<Parent> getParentsByDate(@PathVariable("date") String dateString) {

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    LocalDate dateOccurred = LocalDate.parse(dateString, formatter);

    return parentService.getParentsForDate(dateOccurred);

}

I have not tested the code and I do not know if this way of detaching entities will work, but I think it will likely do.

If this approach does not work, my advice is that you try a different approach.

Instead of querying the parent, just filter the children directly and obtain the ones that match the filter criteria.

@Query("select c from Child c on YEAR(c.date) = :dateYear and MONTH(c.date) = :dateMonth)

On the other hand, list all the Parents (by using findAll, for instance), and create some kind of intermediate POJO, some DTO, to mix the information of the several results obtained, an return that information to the client:

public class ParentDTO {
  private long id;
  private String name;
  private Map<LocalDate, ChildDTO> children = Collections.emptyMap();

  // Setters and getters
}

public class ChildDTO {
  private long id;
  private String name;
  private LocalDate date;

  // Setters and getters
}

  List<Child> children = /* the aforementioned query conforming to filter criteria */
  List<Parent> parents = parentRepository.findAll();

  List<ParentDTO> parentDTOs = new ArrayList(parents.size());
  for (Parent parent:parents) {
    ParentDTO parentDTO = new ParentDTO();
    parentDTOs.add(parentDTO);

    parentDTO.setId(parent.getId());
    parentDTO.setName(parent.getName());

    // Contains parent?
    if (children == null || children.isEmpty()) {
      continue;
    }

    Map<LocalDate, ChildDTO> childrenForThisParent = children.stream()
      .filter(child -> child.parent.equals(parent))
      .map(child -> {
        ChildDTO childDTO = new ChildDTO();
        childDTO.setId(child.getId());
        childDTO.setName(child.getName());
        childDTO.setDate(child.getDate());
        return childDTO;
      })
      .collect(Collectors.toMap(c -> c.getDate(), c));

    parentTO.setChildren(childrenForThisParent);
  }

The code is optimizable in several ways, but I hope you get the idea.