Spring Data JPA Distinct Returning Duplicate Values

In my Spring boot application I have a query which should return a distinct List of Focus’ (works perfectly in MySQL)

@Query(value = "SELECT DISTINCT * FROM Focus F " +
            "JOIN FrameworkFoci FF on FF.focusID = F.focusID " +
            "JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
            "JOIN GroupMembers GM on FG.groupID = GM.groupID " +
            "JOIN Users U on GM.userID = U.userID " +
            "WHERE U.userID = :userID", nativeQuery = true)
List<Focus> findByUserID(@Param("userID") Long userID);

However this does not return distinct values, duplicates are contained in the resulting list. Another issue is that I can’t return a whole entity using @Query annotation – changing my query to SELECT DISTINCT(F) FROM Focus F gives the error java.sql.SQLSyntaxErrorException: Unknown column 'F' in 'field list'.

Furthermore, I tried changing adjusting the query to the following

@Query(value = "SELECT DISTINCT * FROM FrameworkFoci FF " +
        "JOIN FocusGroups FG on FF.frameworkID = FG.frameworkID " +
        "JOIN GroupMembers GM on FG.groupID = GM.groupID " +
        "JOIN Users U on GM.userID = U.userID " +
        "WHERE U.userID = :userID", nativeQuery = true)

however this produced the error java.sql.SQLException: Column 'focusCategory' not found.

Why is the query not returning distinct values? And why can’t I return a whole entity nor use the second query?

Focus Entity:

@Entity
public class Focus {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long focusID;

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

private String focusName;

private String focusExplanation;

@OneToMany(mappedBy = "focus")
private Set<Rating> ratings;

@ManyToMany
@JoinTable(name = "FrameworkFoci",
        joinColumns = @JoinColumn(
                name = "focusID"),
        inverseJoinColumns = @JoinColumn(
                name = "frameworkID"))
private Set<Framework> frameworks;

//image

protected Focus(){}

public Focus(String focusName, String focusCategory, String focusExplanation) {
    this.focusCategory = focusCategory;
    this.focusName = focusName;
    this.focusExplanation = focusExplanation;
}

public Focus(String focusCategory, String focusName, String focusExplanation, Set<Rating> ratings){
    this.focusCategory = focusCategory;
    this.focusName = focusName;
    this.focusExplanation = focusExplanation;
    this.ratings = ratings;
}

public Long getFocusId() {
    return focusID;
}

public void setFocusId(Long focusID) {
    this.focusID = focusID;
}

public String getFocusCategory() {
    return focusCategory;
}

public void setFocusCategory(String focusCategory) {
    this.focusCategory = focusCategory;
}

EDIT:

I’ve switched from SQL to JPQL with the following query:

@Query(value = "SELECT DISTINCT focus FROM Focus focus " +
                "WHERE focus.frameworks.groups.groupMembers.user.userID =:userID ")

I now get an error org.hibernate.QueryException: illegal attempt to dereference collection [focus0_.focusID.frameworks] with element property reference [groups]

Framework entity:

@Entity
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class,
        property = "frameworkID")
public class Framework {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long frameworkID;

    private String frameworkName;

    @ManyToMany
    @JoinTable(name = "FrameworkFoci",
            joinColumns = @JoinColumn(
                    name = "frameworkID"),
            inverseJoinColumns = @JoinColumn(
                    name = "focusID"))
    private Set<Focus> frameworkFoci = new HashSet<>();

    @OneToMany(mappedBy = "framework", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
    private Set<Group> groups;

    public Framework(){}

Answer

The following query solves the issue

@Query(value = "SELECT DISTINCT focus FROM Focus focus " +
                "JOIN focus.frameworks frameworks " +
                "JOIN frameworks.groups groups " +
                "JOIN groups.groupMembers groupMembers "+
                "WHERE groupMembers.userID =:userID ")     
    List<Focus> findByUserID(@Param("userID") Long userID);

Frameworks and GroupMembers are collections and hence needed to be joined, otherwise illegal attempt to dereference collection [focus0_.focusID.frameworks] with element property reference [groups] was produced

Advertisement

Answer