'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:
- i want to filter the task table based on columns
- 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 |
|---|
