'How to solve org.postgresql.util.PSQLException: ERROR: column PRIMARY KEY ID must appear in the GROUP BY clause or be used in an aggregate function

I want to perform the following query using the jpa on spring boot:

  1. i want to filter the task table based on columns
  2. i want to group by the component_id and user_id

Following is the code i used and i have included a groupby and where query statement also :

Task.java ( JPA Repsitory model)


import com.fasterxml.jackson.annotation.JsonBackReference;

import lombok.*;

import javax.persistence.*;
import java.time.LocalDate;

@Entity
@Table(name = "Task")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Task {

    @Id
    @Column(name = "issue_key")
    private String issueKey;

    @Column(name = "issue_id")
    private long issueId;

    @Column(name = "issue_type")
    private String issueType;

    @Column(name = "summary")
    private String summary;

    @Column(name = "created_Date")
    private LocalDate createdDate;

    @Column(name = "updated_Date")
    private LocalDate updatedDate;

    @Column(name = "storyPoints")
    private Double storyPoints;

    @Column(name = "status")
    private String status;

    @Column(name = "priority")
    private String priority;

    @Column(name = "description" , length = 6000)
    private String description;

    @Column(name = "reporter_id")
    private String reporterId;

    @Column(name = "cloners")
    private String cloners;

    @Column(name = "assignee_pair")
    private String assignee_pair;

    @ManyToOne
    @JoinColumn(name = "sprint_id")
    @JsonBackReference
    private Sprint sprint_id;


    @ManyToOne
    @JoinColumn(name = "user_id")
    @JsonBackReference
    private User assignee_id;

    @ManyToOne
    @JoinColumn(name = "component_id")
    @JsonBackReference
    private Component component_id;


    @ManyToOne
    @JoinColumn(name = "project_id")
    @JsonBackReference
    private Project asset_id;

}


code for the group by query:

    public static Specification<Task> getFilterSpecification2(List<FilterCondition> filterConditionList){

        return (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();

            filterConditionList.forEach(filter -> {
                if (filter != null) {
                    if(filter.getOperator().equals(FilterOperationEnum.EQUAL)) {
                        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get(filter.getField()), filter.getValue())));

                    }
                }
                query.where(root.get("component_id").isNotNull());

                query.multiselect(root.get("component_id"), criteriaBuilder.count(root.get("issueKey")));
                query.groupBy(root.get("component_id"));

            });

            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
    }




Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source