Criteria Api: Order/Select by implicit related table

Question

I try to build a CriteriaQuery which provides the following functionality:

I have three tables with the following fields:

table_a:

id, name_a

table_b:

id, name_b

table_ab:

id_a, id_b

Now I want to get all elements out of table_a ordered by the name_b field of the corresponding element in table_b.

The Result should be a Specification for usage in a JpaRepository. I tried using joins, but i stuck at the point, how to combine the joins:

Specification<TableA> specification = (root, query, cb) -> {
        CriteriaQuery<TableAb> abQuery = cb.createQuery(TableAb.class);
        CriteriaQuery<TableB> bQuery = cb.createQuery(TableB.class);

        Root<TableAb> abRoot = abQuery.from(TableAb.class);
        Join<TableAb, TableA> aJoin = abRoot.join("tableA");
        Join<TableAb, TableB> bJoin = abRoot.join("tableB");

        //combine joins

        query.orderBy(cb.asc(/* Expression to order by */));
        return cb.conjunction();
    };

In my opinion the main problem is that there is no "path" from table_a to table_b, but I explicitly do not want to have any reference inside of table_a to table_b.

Answer 1

Since you're using Spring Data JPA , you can just make an interface with a method on it that look like this:

public interface TableABRepository extends Repository<TableAB, Long> {

  public List<TableAB>  findAllByOrderByTableB();

}

Assuming your TableAB class is something like this:

    class TableAB {
        TableA tableA;
        TableB tableB;
    }

Thak method will return all elements from table_ab ordered by the name_b field.

After that you just get the TableA elements from the TableAB returned list.