How to request / submit one-to-many information on a list page

Question

How to request / submit one-to-many information on a 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 display as

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

To build the above table, I would have to do the following to build each row.

 ie 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 extract | cache-caches for the user, so that to create the above list of users does not require more time.

Answer 1

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

Since you tagged your question with hibernate , I assume you are using some kind of JPA. Keyword in JPA JOIN FETCH . A related question may be this .

Answer 2

This is just 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; 

Answer 3

If I understand correctly, what you are actually looking at is a ManyToMany relationship. Many users can use many tags and one cna tag. What bothers you, you are trying to do a JoinTable match as well , instead you can do in your essence:

Tag Element:

 @ManyToMany @JoinTable( name = "user_tags", joinColumns = { @JoinColumn(name = "tag_name") }, inverseJoinColumns = { @JoinColumn(name = "user_id") }) private List<User> users= new ArrayList<>(); 

User object:

  @ManyToMany @JoinTable(name = "user_tags", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = { @JoinColumn(name = "tag_name") }) private List<Tag> productionLines = new ArrayList<Tag>(); 

As you can see, you do not need to deal with the connection table in this case. Maybe I made a typo, so you can find more examples on google or here with the keyword "hibernate ManyToMany JoinTable example"

Answer 4

Retrieving children of the OneToMany relationship in a separate query is the right strategy. Your real problem is related to more performance. To tune performance in this situation, you might consider the following:

• Put the database index in the foreign key field of the child. In your case, it will be the column USER_TAGS.USER_ID . Here is the sql syntax for this:

 CREATE INDEX index_name ON table_name (column_name) 

2. Hard-fetch child objects. If you show your users in a tree, do not load tags until you parse a specific user node. If you want to display tags in a grid or list, consider using a grid / part layout where you only show tags when a user is selected in the grid.

3. If you have many children, use paging. This will require a two-step request. (1) Request your user, then (2) request page = 1, use pageSize = 10 of your user tags and show the next page or more button to make another call to get 11-20 records.

4. Use a cache layer. If your data is static, use NGiNX . Or, if your data is dynamic, look at database caching like EHCache , or use a high-performance NoSQL database like Onyx Database . Onyx intuitively supports the cache for you.

5. If you have an index and lazily upload your entries, you should not experience too much latency. If you are ruthless, then this is either because you are eager to pull too many other related objects or are dealing with "big data." If you use byte-sized data, I would look at breaking up and clustering your data into logical fragments.

6. If you must get both users and user_tags in the same call, the LEFT OUTER JOIN is what you are looking for.

Answer 5

Use LEFT JOIN FETCH when loading a query to retrieve both the parent entity and the child collection. Assuming the User object has a matching List<Tag> tags collection, the query should look like this:

 Select u from User u LEFT JOIN FETCH u.tags 

This can be done directly using Hibernate, or if you are using JPA, you can add it to your repository via a separate method annotated using @Query

 @Query("Select u from User u LEFT JOIN FETCH u.tags") List<User> findAllWithTags()