Convert postgresql join and group by query to JPA criteria API

user_16658092 0
Viewed 471

I am having trouble to converting the following postgresql query (with a join and a group by) to JPA criteria API for a Spring Boot, JPA, Hibernate application:

select u.id, u.full_name, count(*) project_applications_count from users u
join project_applications pa on pa.created_by = u.id
group by u.id, u.full_name
having count(*) >= 1 and count(*) <= 5

The tables look like this:

create table project_applications (
    id serial primary key,
    ...
    city_id integer not null references cities (id),
    created_by integer not null references users (id)
);

create table users (
    id serial primary key,
    ...
    full_name varchar(100) not null
);

And the entities look like this:

@Entity
@Table(name = "project_applications")
public class ProjectApplication {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "created_by")
    private User createdBy;

    ...
}

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "full_name")
    private String fullName;

    ...
}

I tried searching online for a solution but every exemple I found was using either a join or group by, but not both.

criteria-api jpa postgresql hibernate java
3 Answers

You could potentially look into projections in order to achieve what you want.

For example consider the following projection and repository:

@Data
@AllArgsConstructor
public class ProjectApplicationSummary {

    private Long id;
    private String fullName;
    private Long count;

}

And:

@Repository
public interface ProjectApplicationRepository extends JpaRepository<ProjectApplication, Long> {

    @Query(
        """
        SELECT new com.example.springdemo.entities.ProjectApplicationSummary(u.id, u.fullName, count(pa))
        FROM User u, ProjectApplication pa
        GROUP BY u.id, u.fullName
        """
    )
    List<ProjectApplicationSummary> getSummaries();

}

You will most likely need to tweak the query a bit (which revolves experimenting with JPQL) but other than that, the basic idea is there.

user_29259620

I'm not sure in my solution, but it should be similar. I took an idea from here. Maybe it helps you to resolve your problem.

    public static Specification<User> getUsers() {
            return Specification.where((root, query, criteriaBuilder) -> {
                CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);

                Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
                Root<ProjectApplication> subRoot = subQuery.from(ProjectApplication.class);

                subQuery
                    .select(criteriaBuilder.count(subRoot))
                    .where(criteriaBuilder.equal(root.get("id"), subRoot.get("createdBy").get("id")));

                query
                    .multiselect(criteriaBuilder.construct(root.get("id"), root.get("fullName")))
                    .groupBy(root.get("id"), root.get("fullName"))
                    .having(criteriaBuilder.and(
                            criteriaBuilder.greaterThanOrEqualTo(subQuery.getSelection(), 1L),
                            criteriaBuilder.lessThanOrEqualTo(subQuery.getSelection(), 5L)));

                return query.getRestriction();
        });
    }
user_152026450

Using @akortex's idea with projections, I think something like this should work:

public class UserSummary {
    private Long id;
    private String fullName;
    private Long count;

    public UserSummary() {
    }

    public UserSummary(Long id, String fullName, Long count) {
        this.id = id;
        this.fullName = fullName;
        this.count = count;
    }

    ... (getters and setters)

}

public List<UserSummary> getSummaries(Integer minProjectAppsCount, Integer maxProjectAppsCount) {
    CriteriaBuilder cb = _entityManager.getCriteriaBuilder();
    CriteriaQuery<UserSummary> query = cb.createQuery(UserSummary.class);  

    Root<ProjectApplication> projectApp = query.from(ProjectApplication.class);
    Join<ProjectApplication, User> userJoin = projectApp.join("createdBy", JoinType.INNER);
    query.multiselect(userJoin.get("id"), userJoin.get("fullName"), cb.count(projectApp))
        .groupBy(userJoin.get("id"), userJoin.get("fullName"));

    List<Predicate> predicates = new ArrayList<>();
    if (minProjectAppsCount != null ) {
        Predicate p = cb.ge(cb.count(projectApp), minProjectAppsCount);
        predicates.add(p);
    }

    if (maxProjectAppsCount != null ) {
        Predicate p = cb.le(cb.count(projectApp), maxProjectAppsCount);
        predicates.add(p);
    }

    
    query.having(predicates.toArray(new Predicate[0]));

    return _entityManager.createQuery(query).getResultList();
}
user_81756610
Related