How to query/represent 1-to-many information in the list page

Question

How to query/represent 1-to-many information in the list page

create users (id, name, ...)
create user_tags (id, user_id, tag_name)

user has 1 to many user_tags

Sample data - I would like the table to be rendered as

user1 tag1,tag2
user2 tag3
user3 tag1
user4
user5 tag5,tag6.tag7

In order to construct the above table, I would have to do the following to construct each row.

i.e.
select * from users;
for (User user : users) {
  // select tag_name from user_tags where user_id = user.id
  List<Tag> tags = fetchtags(user)
}

Is there a better way to fetch | cache tags for an user, so that it doesn't take a longer time to construct the above list of users.

Answer 1

The main performance issue in your approach is you query the database N+1 times. Once for all the users and N-times for the usertags (N is the number of users). You should use one single select statement where you join both tables.

Since you tagged your question with hibernate I suppose you use some kind of JPA. The keyword in JPA is JOIN FETCH. A related question might be this one.

Answer 2

This is simple SELECT with GROUP_CONCAT():

SELECT u.name,GROUP_CONCAT(DISTINCT tag_name) AS tags 
FROM users u
LEFT JOIN user_tags t ON (t.user_id=u.id)
GROUP BY u.name;