For a JPA entity with many to 1 relation to a data entity, how to sort a list of objects by the resu-CodePudding

I have a JPA entity User, which contains a OneToMany relation to a UserStats entity. I have refactored the existing code to use this new UserStats entity and previously to manage the victory ratio dependent features I was storing the victoryRatio within the User entity as a property.

However, I decided it would be better to simply have a method return the victoryRatio, and so calculateVictoryRatio() was implemented into the UserDetails entity.

This has caused some issues, for example, I had a repository method within the UserRepo:

List<User> findAllByOrderByVictoryRatioDesc();

I can no longer search the repo for this directly as the victoryRatio() method is within the UserStats of the User entity, and I don't think you can call methods in a repo method anyway?

So I am now trying to do within userService, but can't figure it out:

public List<User> getBestVSUsers() {

    List<User> users = userRepository.findAll();
    for (User user: users
         ) {
        user.getUserStats().get(0).calculateVictoryRatio();
    }

}

But, I need to sort the above list based on the result of calculateVictoryRatio().

Also in my React frontend I was rendering this:

   const topUserList = users.map(user => {
            return <tr key={user.id}>
                <td style={{ whiteSpace: 'nowrap' }} >{user.name}</td>
                <td className="player-list-key-text">{user.username}</td>
                <td className="player-list-key-text"><span className="defeats">{user.victories}</span></td>
                <td className="player-list-key-text"><span className="victories">{user.defeats}</span></td>
                <td className="player-list-key-text"><span className="victory-score">{user.victoryRatio}</span></td>
            </tr>
        });

How can I render the victoryRatio, defeats and victories as above now they are encapsulated within the UserStats entity? Do I need to do a seperate fetch and combine the 2? Or can I do something like:

{user.userStats[0].victories}

How can I do this? Is there a better way to do this? Can I call methods within the userRepo?

Thanks for the help

CodePudding user response：

Try something like this:

PageRequest pageRequest = new PageRequest(pageNumber, pageSize, new Sort(new Order(Direction.DESC, "ORDER BY (VICTORIES/DEFEATS)"))
Page page = myRepository.findAll(specification, pageRequest);

Where on ORDER BY you can put your ratio formula. In this case, we put the calculate on you SGDB.

CodePudding user response：

You can order on a related entity as long as you join that entity in the query. You may need to define a custom query in your repository.

@Query("SELECT u from User u JOIN u.userStats us ORDER BY us.victoryRatio")
List<User> findAllCustomSort();

However, obviously the database engine does the sorting here, independently of your code. You haven't shown us the contents of your calculateVictory method but if you must sort based on a calculation you do in code, it won't work. I doubt this is the case since you're just talking about moving the ratio to another entity, not starting to calculate it instead. But if so, you could of course always just sort the list in memory. Considering you're not using pagination, this wouldn't be too bad either.

Or if you absolutely must sort in the database, then I don't think you'll have any choice but to create a custom function for it in the database. That would go a bit beyond the scope of this question though.