0

I am trying to code the matchmaking feature of my game web-app, the User entity contains a property, UserStats with a OneToMany relationship which stores a list of 2 UserStat JPA entitites, these userStat entities have 2 different sets of stats, and one of the stats I require is called victoryScore.

How can I search for Users where their UserStats.get(0).victoryScore is a specific value/range of values?

I think I need to use JOIN possibly, here is my attempt:

 @Query(
            value = "SELECT * FROM user u JOIN u.userStats us WHERE u.location = :location AND u.rating BETWEEN :lowerRatingBound AND :upperRatingBound AND us.victoryScore BETWEEN :lowerVictoryScoreBound AND :upperVictoryScoreBound",
            nativeQuery = true)
    List<User> findChallengingUsers(@Param("lowerRatingBound") Integer lowerRatingBound, @Param("upperRatingBound") Integer upperRatingBound, @Param("lowerVictoryScoreBound") Double lowerVictoryScoreBound, @Param("upperVictoryScoreBound") Double upperVictoryScoreBound, @Param("location") String location);

And the error:

No property victoryScore found for type User!

User:

@Entity(name = "User")
@Table(name = "user")

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {

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

@Column(name = "username", unique = true)
private String username;

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

private String name;
private String email;
private String location;
private String bio;
private Integer rating;


@OneToMany(fetch = FetchType.EAGER, mappedBy = "user", cascade = CascadeType.PERSIST)
private List<UserStats> userStats;
}

UserStats:

    @Entity()
@Table(name = "userStats")


  @Getter
    @Setter
    @AllArgsConstructor
    @NoArgsConstructor
public class UserStats {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long userStatsId;

    private Integer victories;
    private Integer draws;
    private Integer defeats;

    private Double victoryScore;

private boolean isLeagueUserStats;

@JsonIgnore
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn(name = "user_id")
private User user;
}

Is this even possible?

For reference, I access the victoryScore value for how I want it to search for using:

user.getUserStats(0).getVictoryScore;

The user stats class is also a domain entity so also has its own repository interface.

Any help would be greatly appreciated, thanks!

devo9191
  • 219
  • 3
  • 13
  • 1
    Try running your query against the db using a db console, you'll find it's not a valid query. – Igor Flakiewicz Jan 20 '22 at 18:03
  • @IgorFlakiewicz I know it's not a valid query it was just my attempt from what I can find online about similar situations, I just don't know how to actually do this. – devo9191 Jan 20 '22 at 18:24
  • 1
    Learn about joins, write a valid query via trial and error, copy paste the query into your `@Query` annotation. JPA will do the mapping as your code is, just needs a valid query in your SQL flavour. – Igor Flakiewicz Jan 20 '22 at 18:26
  • Why do you set native query=true? You seem to mix and match JPQL with SQL: Use JPQL and your entities and let JPA handle the joins based on your mappings for you. Something along the lines of "select u from User u JOIN u.userStats us where ..". Difference is just the select - specify User instances and use the Entity names in the string. As for the mappings, User.userStats doesn't look correct as you don't have a UserStats.user in the java code you posted (you specify mappedby="user" which means it must be defined). – Chris Jan 20 '22 at 18:59
  • Using get(0) though cannot work, as the User.userStats list itself is non-deterministic and has no ordering or other fields you can use to figure out what is indexed 0. If you want to have one designated as the 'first' in the list, you must add more to the relationship and the entities to mark it - which you can then use in your queries. – Chris Jan 20 '22 at 19:01
  • @Chris This was just a small representation of the code, the UserStats class contains a a User as well as a boolean to differentiate whether it is or not 'isCompetitiveUserStats', I will update question with code, I didn't really leave that stuff out on purpose it was just below what I could see and I forgot they were there as well (didn't put all my fields in one contiguous block). Thanks. – devo9191 Jan 20 '22 at 19:05
  • @Chris also, as for the native query = true, that was simply what the tutorial showed me to do when I looked up SQL queries in JPA, and have used it on all my other queries like this one. Also IsLeagueUserStats == true == userStats.get(1) – devo9191 Jan 20 '22 at 19:08
  • @Chris Could I do something like: SELECT u FROM User u JOIN u.userStats us WHERE us.isCompUserStats = false AND where u.location: location AND us.victoryScore BETWEEN... etc? – devo9191 Jan 20 '22 at 19:14
  • Try it, seems fine to me (except for some cut and past issues). There are many examples showing the JPQL functions like between, and it should work on a double/numeric values. As for IsLeagueUserStats == true == userStats.get(1) - this isn't represented in the user stats list at all, so I hope your code doesn't rely on that being true. You would have to add ordering to the userStats mapping using something like OrderBy("IsLeagueUserStats") annotation on the mapping. Regardless, just add "us. IsLeagueUserStats = false" to your where clause if that is the one you want to filter on. – Chris Jan 20 '22 at 19:23
  • @Chris Alright thanks for your help, the userStats.get(1) thing is because in the service classes I declare them both as variables and get them like that and when I create the userStats List to go into user, I put the leagueUserStats in index 1. Thanks. – devo9191 Jan 20 '22 at 19:35
  • @Chris Actually I still get the same error No property victoryScore found for type User! And this must be because us.victoryScore is a list of userstats, not an actual userstat object, is there some way to access that in this query? thanks – devo9191 Jan 20 '22 at 19:39
  • @Chris I know this is probably bad practice but I could get all the users in that location, rating and then loop through that list getting all the victory scores and removing any that aren't between the upper and lower bounds? Obviously that could be a lot of users in theory – devo9191 Jan 20 '22 at 19:49
  • Show the actual query you are executing and the error stack from what you are now using in the question, as it doesn't make sense that it is coming from the 'us.victoryScore BETWEEN :lowerVictoryScoreBound AND :upperVictoryScoreBound' clause. Try a basic user query with a join to UserStats, but I suspect it is something else with your repo, more along the lines of https://stackoverflow.com/a/26539097/496099 – Chris Jan 21 '22 at 15:20

1 Answers1

1

Try this query,

Query(value ="SELECT * FROM user u INNER JOIN userStats us ON u.id = us.user_id where (u.location = :location AND u.rating BETWEEN :lowerRatingBound AND :upperRatingBound) AND (us.victoryScore BETWEEN :lowerVictoryScoreBound AND :upperVictoryScoreBound)",nativeQuery = true)
Ashwini Karnale
  • 327
  • 1
  • 5