I ran into a similar problem to this question:
I have a table of teams, and each team participates in events, which are noted in an event participation table that includes the team's ID and the number of points the team earned. It's set up like this:
Team:
int id
Event_Participation:
int id
@ManyToOne
@JoinColumn(name = "team_id")
Team team
The problem arises when I attempt to retrieve a list of all teams and their corresponding point totals. The SQL for something like this is easy, and I put this in my Repo:
@Query(value = "SELECT teams.*, Sum(event_participation.points) as points
FROM teams LEFT JOIN event_participation
ON event_participation.team_id = teams.team_id
GROUP BY teams.team_id", native_query = true)
Where my problem changes is that I want to try and avoid the solution given, since I'm getting a list of all this data, I want to be able to send as little data as possible, and sending all the data from Event_Participation alongside a method to go through the list of data seems inefficient.
If I'm just being paranoid, please let me know. I haven't run into a situation like this, where I'm working with a large amount of data. Any/all advice and assistance are very much appreciated.
Usually a sort of projection is required to support this functionality. @Formula could be used as well but I imagine you dont want to calculate the points every time the Team entity is loaded.
A simple interface projection would be as follows:
The interface used for projection.
interface TeamScore {
Team getTeam();
Long getPoints();
}
In the Team repository, using JQPL instead of native query.
@Query("SELECT " +
"t AS team, " +
"SUM(ep.points) AS points " +
"FROM Team t " +
"LEFT JOIN EventParticipation ep " +
"ON ep.team = t " +
"GROUP BY t")
List<TeamScore> getTeamScores();
Click the link for more info on JPA's projections.