How to make "order by aggregate function" in Spring Data?

java, jpa, spring, spring-data, spring-data-jpa

Solution

You can use your custom repository implementation along with Crud/PagingAndSorting repository implementation, like this:

End-point repo:

public interface ResourceFileRepository extends
    PagingAndSortingRepository<ResourceFile, Integer>,
    ResourceFileRepositoryCustom {
}

Custom repo:

public interface ResourceFileRepositoryCustom {
    List<ResourceFile> getResourceFilesOrderByFavourites();
}

Custom repo implementation with actual code to retrive ResourceFile's ordered by favourite count (notice it is ResourceFileRepositoryImpl and not ResourceFileRepositoryCustomImpl).

I didn't have those embeded keys, so I had to simplify it a little bit. The query is going FROM the FavoriteResourceFile, because ResourceFile don't have own relation to FavoriteResourceFile.

public class ResourceFileRepositoryImpl implements ResourceFileRepositoryCustom {
    @PersistenceContext
    private EntityManager em;

    public void setEntityManager(EntityManager em) {
        this.em = em;
    }

    @Override
    public List<ResourceFile> getResourceFilesOrderByFavourites() {
        CriteriaBuilder criteriaBuilder = this.em.getCriteriaBuilder();
        CriteriaQuery<ResourceFile> q = criteriaBuilder
                .createQuery(ResourceFile.class);
        Root<FavoriteResourceFile> root = q.from(FavoriteResourceFile.class);
        Join<FavoriteResourceFile, ResourceFile> join = root.join(
                 FavoriteResourceFile_.resourceFile, JoinType.LEFT);
        q.select(join);
        q.groupBy(join.get(ResourceFile_.id));
        q.orderBy(criteriaBuilder.desc(
                      criteriaBuilder.count(
                          join.get(ResourceFile_.id))));

        TypedQuery<ResourceFile> query = this.em.createQuery(q);
        return query.getResultList();
    }
}

To see full example project (with some very basic sql and test) - checkout/fork/etc: https://github.com/rchukh/StackOverflowTests/tree/master/13669324

Problem

I have two entities: ResourceFile: ``` @Entity @Table(name = "resource_file") public class ResourceFile extends IdEntity<Integer> { @Id @SequenceGenerator(name = "resource_file_id_generator", sequenceName = "resource_file_id", allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "resource_file_id_generator") @Column(name = "id", unique = true, nullable = false) @Nonnegative private Integer id; ... } ``` FavoriteResourceFile: ``` @Entity @Table(name = "favorite_resource_file") @Cacheable @Cache(usage = CacheConcurrencyStrategy.READ_WRITE) public class FavoriteResourceFile extends IdEntity<FavoriteResourceFileId> { @EmbeddedId private FavoriteResourceFileId id; @MapsId("resourceFileId") @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "resource_file_id", nullable = false) private ResourceFile resourceFile; ... } ``` And I want to make the following query "select all resource files and sort them by favourite resource file's count". In SQL it looks like: ``` select rf.id, count(frf.resource_file_id) from resource_file rf left join favorite_resource_file frf on frf.resource_file_id = rf.id group by rf.id order by count(rf.id) desc; ``` But I can't understand how to do it with Spring Data and how to make mapping to ResourceFile entity at the end. Some limitations: - I can't make relation to FavoriteResourceFile in ResourceFile, because they are located in different modules - I don't want to use native SQL or JPA query (as strings). - It'll be preferable to use Meta-models, Specification or QueryDSL, because they are already used in project. Can someone help me?

Original source