Query specified Join fetching, but the Owner of the fetched Association was not present in the select list

Question

I have a scenario with the following entities:Treinamento, Curso and Aulas.
A Treinamento has a relationship ManyToOne for Curso.
And Aula has a relationship ManyToOne for a Curso.

I want to perform a query that given a certain training id return me the training with your course and with your respective lessons filled in the object. I’m doing the consultation as follows:

Treinamento retorno = null;

    StringBuilder sb = new StringBuilder();
    sb.append("SELECT t FROM Treinamento t ");
    sb.append("INNER JOIN t.idCurso c ");
    sb.append("LEFT JOIN FETCH c.aulas a ");
    sb.append("WHERE t = '"+String.valueOf(treinamento)+"' ");

    retorno = (Treinamento)this.em.createQuery(sb.toString()).getSingleResult();

And I’m getting the bug:

Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=a,role=br.com.empresa.domain.ptreinamentos.Curso.aulas,tableName=Aula,tableAlias=aulas2_,origin=Curso curso1_,columns={curso1_.id ,className=br.com.empresa.domain.ptreinamentos.Aula}}]

The error tells me that the owner of the fetch relationship is not in the select clause. In this case the owner of the relationship would be the Curso. What would be the correct way to perform this consultation ?

Answer 1

The solution to this problem is that when performing a JOIN FETCH one should start doing the FETCH from the first relationship, in my case the query would be as follows:

Treinamento retorno = null;

StringBuilder sb = new StringBuilder();
sb.append("SELECT t FROM Treinamento t ");
sb.append("INNER JOIN FETCH t.idCurso c ");
sb.append("LEFT JOIN FETCH c.aulas a ");
sb.append("WHERE t = '"+String.valueOf(treinamento)+"' ");

retorno = (Treinamento)this.em.createQuery(sb.toString()).getSingleResult();

Answer 2

This situation is very similar to a case of count in a query paginated. The correct response to this type of situation is to declare the consultation of count without the FETCHs and add them in order to avoid the N+1 problem only in the object search query. This is the answer in English: https://stackoverflow.com/a/54583594/10746857.