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. Following are my thoughts
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.
I have considered creating DTO as projection to aggregate data for question and user profile card into a single 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.
- 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 downside, 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 solution.
What else? Is there any other approach I am missing that is good from code maintainability as well as performance perspective?