Right approach for aggregating data from multiple tables in spring JPA

See more:

Java

JPA

Spring

Hibernate

I have a Q&A section where each question and answer has a user profile card associated with it. Which displays username, points etc. Data that makes up this user profile card is spread across multiple tables. I am fetching one or two columns from each of these tables. Now I am not sure what approach I should take to associate user profile card with each question and answer.

Thanks

What I have tried:

1. I cannot have a straightforward association in question and answer entity since user profile card itself is not a single entity but is an aggregate object
2. I have considered creating DTOs as projections to aggregate data for question and user profile card into one DTO like below

@Query("select new com.QuestionDto(q.id, q.topic, q.views, ud.username, ur.points) from QuestionEntity q join UserDetails ud ON q.userId = ud.userId join userRanking ur ON ud.userId = ur.userId where q.id = :id")
QuestionDto findQuestionDto(@Param("id") Long id);

The problem I see with this approach is there will be such DTOs for answer, comments etc as well. So whenever there is a change to user profile card I will need to make those changes to all the DTOs accordingly. Which will be a very poor design from code maintainability perspective.

3. Another approach is to aggregate user profile card data into a DTO of itself and then this DTO will be called from each question, answer etc. With this approach at least the logic for user profile card is at one place.

@Query("select new com.UserProfileCard( ud.username, ur.points) from UserDetails ud join userRanking ur ON ud.userId = ur.userId where ud.userId = :id")
UserProfileCard getUserProfileCard(@Param("id") Long userId);

But on the flip side If a question has 15 replies, that will lead to (1 + 15) queries for each user. From performance perspective I don't see this as very efficient.

What else? Is there any other approach I am missing that is good from code maintainability as well as performance perspective?