Reputation: 6110
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.
Upvotes: 7
Views: 356
Reputation: 16034
Fetching the children of a OneToMany relationship in a separate query is a perfectly valid strategy. Your real issue is more performance related. To performance tune in this situation you may want to consider the following:
USER_TAGS.USER_ID
column. Here is the sql syntax to do that: CREATE INDEX index_name
ON table_name (column_name)
Lazily retrieve your child objects. If you are displaying your users in a tree, do not load the tags until you expand a particular user node. If you want to display the tags in a grid or list, consider using a grid/detail layout where you only display the tags when a user in the grid is selected.
If you have a lot of child objects, use paging. This would require a two-phased request. (1) Request your user, then (2) request page=1, with pageSize=10 of your userTags, and display a next page
or more
button to make another call to retrieve records 11-20.
Utilize a caching layer. If your data is static look into using NGiNX. Or if your data is dynamic look into database caching like EHCache or use a high performance NoSQL database like Onyx Database. Onyx intuitively maintains a cache for you.
If you have an index and you are lazily loading your records, you shouldn't be experiencing too much latency. If you are reguarless, then it is either because you are eagerly pulling too many other related objects or you are dealing with "Big Data". If you are using petabyte scale data, I would look into partitioning and clustering your data into logical shards.
If you must retrieve both the users
and the user_tags
in the same call, a
LEFT OUTER JOIN
is what you are looking for.
Upvotes: 1
Reputation: 2528
Use LEFT JOIN FETCH in loading query to fetch both parent entity and children collection. Assuming that User
entity has mapped List<Tag> tags
collection, the query should look like that:
Select u from User u LEFT JOIN FETCH u.tags
This can be executed directly by Hibernate or if you are using JPA, you may add it to your repository via separate method annotated with @Query
@Query("Select u from User u LEFT JOIN FETCH u.tags")
List<User> findAllWithTags()
Upvotes: 0
Reputation: 2790
If I understand right, what you are looking actually is ManyToMany relationship. One User can have many tags and one Tag cna be used by many users. What confuses you is you try to make mapping also to JoinTable, instead you can do in your entity:
Tag entity:
@ManyToMany
@JoinTable(
name = "user_tags",
joinColumns = { @JoinColumn(name = "tag_name") },
inverseJoinColumns = { @JoinColumn(name = "user_id") })
private List<User> users= new ArrayList<>();
User entity:
@ManyToMany
@JoinTable(name = "user_tags", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = { @JoinColumn(name = "tag_name") })
private List<Tag> productionLines = new ArrayList<Tag>();
As you see you dont have to deal with join table in this case. It is possible I did a typo here so you can find more examples in google or here with the keyword 'hibernate ManyToMany JoinTable example'
Upvotes: 1
Reputation: 12818
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;
Upvotes: 1
Reputation: 820
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.
Upvotes: 2