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(){}

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

you should write your query like this:
'
SELECT DISTINCT f FROM Focus F '

The problem stems from you using SQL by specifying nativeQuery = true. SQL doesn't know about entities, just tables.

Since you presumably have many FrameworkFoci rows (and rows in all the other tables) for each Focus row, each Focus row gets repeated for each matching row in FrameworkFoci. This kind of duplicates the Focus row but the resulting rows are still distinct, because they differ in the columns from the other tables.

And then each row gets turned into a Focus entity, probably with a single element in the framework set.

So therefore query doesn't so much return duplicate results as results split into multiple entities.

Fortunately the solution should be fairly simple: Use JPQL which should be perfectly possible, since you're using only simple joins.

The following should give you a start:

@Query(value = "SELECT DISTINCT * FROM Focus F " +
"WHERE F.framework.groupMembers.user.id=:userID")
List<Focus> findByUserID(@Param("userID") Long userID);