Click here to Skip to main content
15,921,028 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.


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.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 = :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?
Updated 4-Jan-20 19:55pm

1 solution

The id of the user should go against the question or answer, that seems obvious. If you need data across many tables to display this, create views
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900