Spring Boot JPA "Validation failed for query" error for JPQL

Question

I'm trying use a custom query with jpa (not a nativeQuery since I want to map the result to a custom object) and I can't figure out whats wrong.

The repository class looks like this:

@Repository
public interface ChallengeCompletionRepository extends JpaRepository<ChallengeCompletion, Integer>{
    List<ChallengeCompletion> findByUser(int uid);
    List<ChallengeCompletion> findByChallenge(int cid);
    List<ChallengeCompletion> findByUserAndChallenge(int uid, int cid);

    @Query(value = "SELECT new com.some.rly.long.package.name.ChallengeScore(user_id, count(id)) " +
        "FROM ChallengeCompletion " +
        "WHERE challenge_id = :cid " +
        "GROUP BY user_id " +
        "ORDER BY count(id) DESC")
   List<ChallengeScore> fetchUserScoreForChallenge(@Param("cid") int cid);
}

And the model class I want the resultl listed in looks like this:

@Data
@NoArgsConstructor
public class ChallengeScore {

    public ChallengeScore(UUID userId, int score){
        this.score = score;
        this.userId = userId;
    }

    private int score;
    private User user;

    @JsonIgnore
    private UUID userId;

}

This is the model for ChallengeCompletion:

@Entity
@Data
@Table(name = "challenge_completions")
public class ChallengeCompletion extends BaseModel{

    @ManyToOne
    @JsonBackReference
    private User user;

    @ManyToOne
    private Challenge challenge;

    @ManyToOne
    private Project project;

}

And the base model is:

@MappedSuperclass
@Data
public abstract class BaseModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @CreationTimestamp
    private Timestamp createdAt;

    @UpdateTimestamp
    private Timestamp updatedAt;
}

The error is simply: "Validation failed for query...". I also feel it is a bit strange that I need to use the fully qualified name for the class I want to construct,.. but this is probably due to the fact the class is not a real @Entity and doesn't get autoloaded.

A full stacktrace can be found here: https://pastebin.com/MjP0Xgz4

For context, what this Query should do is: A user can complete challanges multiple times. Every completion is logged as a row in ChallengeCompletion. I want to get a list of distinct user(ids) and how often they completed a certain challenge.

Cheers

EDIT: Thanks to the comment by @DN1 I could get it working after finding out that you can indeed do something like cc.challenge.id if you do not want to give a Challenge object but rather only an id:

@Query(value = "SELECT new com.energiedienst.smartcity.middleware.module.challenge.model.ChallengeScore(cc.user, count(cc.id)) " +
        "FROM ChallengeCompletion cc " +
        "WHERE cc.challenge.id = :cid " +
        "GROUP BY cc.user " +
        "ORDER BY count(cc.id) DESC")
List<ChallengeScore> fetchUserScoreForChallenge(@Param("cid") int cid);

And the ChallengeScore Class now looks like:

@Data
@NoArgsConstructor
public class ChallengeScore {

    public ChallengeScore(User user, long score){
        this.score = score;
        this.user = user;
    }

    private long score;
    private User user;

}

Answer 1

The reason for the exception is that you are using JPQL, and JPQL uses class/field names and not table/column names (what SQL uses). The user_id and challenge_id are column names seemingly, and you need to change these to use field names. See this guide for details.