spring query dsl: how to sort by two database fields, one of the being foreign key?

Question

I'm using Spring 4.1.6 with QueryDSL 3.7.4.

I have created my Entities with Telosys Tools, and my QueryDSL entities with the apt-maven-plugin.

I didn't used the @Query tag because I think if I do, I can't get a Page of results, and by performance reasons, I have to do it without the @Query annotation (maybe I'm confused about that).

I put here a snippet of my entities code:

IncidenciasEntity.java:

@ManyToOne
@JoinColumn(name = "ID_TIPO_INCIDENCIA", referencedColumnName = "ID_TIPO_INCIDENCIA")
private TiposIncidenciasEntity tiposIncidencias;

TiposIncidenciasEntity.java

//----------------------------------------------------------------------
// ENTITY PRIMARY KEY ( BASED ON A SINGLE FIELD )
//----------------------------------------------------------------------
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ID_TIPO_INCIDENCIA", nullable=false)
private Integer    idTipoIncidencia ;

My web application is working fine but there is one thing I can't achieve: I want to sort and paginate the output of a large MySQL table, where one of the fields I want to sort by is a foreign key. I've performed two approaches:

Approach 1:

public Page<IncidenciasEntity> obtenerPaginaIncidenciasOrdenYPaginacionInicial(int paginaMas1,
            int numIncidencias)
    {   
        OrderSpecifier<Integer> sortOrder1 = QIncidenciasEntity.incidenciasEntity.idIncidencia.asc();
        OrderSpecifier<Integer> sortOrder2 = QIncidenciasEntity.incidenciasEntity.tiposIncidencias().idTipoIncidencia.asc();

        QSort qsort = new QSort(sortOrder1, sortOrder2);

        return incidenciasRepo.findAll(new QPageRequest(paginaMas1 - 1, numIncidencias, qsort));
    }

Approach 1 produces the error: No property idTipoIncidencia found for type IncidenciasEntity!

Approach 2 (no QueryDSL, just Spring Data JPA):

public Page<IncidenciasEntity> obtenerPaginaIncidenciasOrdenYPaginacionInicial(int paginaMas1,
            int numIncidencias)
    {
        Order criterioOrdenacion1 = new Order(Direction.ASC, "idIncidencia");
        Order criterioOrdenacion2 = new Order(Direction.ASC, "tiposIncidencias.idTipoIncidencia");

        Sort criterioOrdenacionMixto = new Sort(criterioOrdenacion1, criterioOrdenacion2);

        return incidenciasRepo.findAll(
                new PageRequest(paginaMas1 - 1, numIncidencias, criterioOrdenacionMixto));
    }

Approach 2 does not display any errors, but is not sorting by the field idTipoIncidencia as I wanted.

Note: I don't want to make it work QueryDSL only, I want to make it work no matter how, even with a jpa repository.

Answer 1

If your repository extends PagingAndSortingRepository, simply findAll(new PageRequest(page, size, new Sort("idIncidencia", "tiposIncidencias"))) should be sufficient. idIncidencia will translate to the ID_INCIDENCIA column and tiposIncidencias will translate to the ID_TIPO_INCIDENCIA column automatically.

I have created a sample application with your code that demonstrates the correct sorting order with a simple call to findAll. The sample contains a unit test that adds some records to the tables, retrieves them in the required sorting order and then checks that they were actually returned in the required order.