1

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;

}
Tom
  • 3,807
  • 4
  • 33
  • 58
  • Have you tried using projections (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections) or writing a custom implementation – pvpkiran Sep 14 '17 at 09:53
  • @pvpkiran, thanks for the answer, not sure what you mean with custom implementaion. As far as I understnad the above case should work. I do have this problem that I need an intermediary class for custom queries quite often, so I would like to understand the problem instead of working around it. The error says there's something synthactically wrong with the query. – Tom Sep 14 '17 at 10:03
  • can you put complete stack strace. Projection is specifically used in case of intermediary classes. What I meant is you can write a custom repository and convert `ChallengeCompletion` to `ChallengeScore` https://stackoverflow.com/questions/43265331/propertyreferenceexception-in-custom-repository/43273248#43273248 – pvpkiran Sep 14 '17 at 10:08
  • you cant have JPQL with "user_id" when there is no such FIELD in the class. This is not SQL. But then you dont post the JPA entity ChallengeCompletion that this is allegedly for –  Sep 14 '17 at 10:28
  • @DN1 i think you are mistaken about "user_id": that is a field of ChallengeCompletion as it should be and not of ChallengeScore... that is if it is actually a field of the class so... yeah post the entity so we got the full picture – Zeromus Sep 14 '17 at 10:48
  • @DN1 I added the classes, sry about that, totally forgot to include them in the original post. the user_id field is a column in the table challenge_completions, mapping the referenced user. I can get it working by making it a `nativeQuery`, returning a `List` and getting the fields out of there, it's just I wanted a more clean approach. – Tom Sep 14 '17 at 11:00
  • So the JPQL is indeed INVALID. You should refer to the "user.id" field (if that exists) rather than "user_id". JPQL uses FIELD names not COLUMN names. Hence why using native (SQL) works but JPQL doesnt. Same goes for "challenge_id". –  Sep 14 '17 at 11:21
  • @DN1, thanks, got it working after some tinkering with your comment, youre welcome to put that in an answer so i can accept it. – Tom Sep 14 '17 at 12:12

1 Answers1

4

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.