7

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.

Rpj
  • 5,348
  • 16
  • 62
  • 122

5 Answers5

2

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.

Community
  • 1
  • 1
frifle
  • 810
  • 5
  • 24
1

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;
vhu
  • 12,244
  • 11
  • 38
  • 48
  • 1
    Mind that `GROUP_CONCAT()` is mysql-only. For other DBS see [http://www.vertabelo.com/blog/technical-articles/group-concat]. – frifle Oct 16 '15 at 09:33
1

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'

HRgiger
  • 2,750
  • 26
  • 37
1

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:

  1. Put a database index on the foreign key field of your child entity. In your case this would be the USER_TAGS.USER_ID column. Here is the sql syntax to do that: 
CREATE INDEX index_name
ON table_name (column_name)

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

cosbor11
  • 14,709
  • 10
  • 54
  • 69
  • those are all general guidelines, but have little common with the question, only item 6 has value – Vasily Liaskovsky Oct 23 '15 at 11:21
  • 1
    @VasilyLiaskovsky, gonna have to disagree with you on that. I think you will find that even by doing an outer join you gain very little if any performance benefit. – cosbor11 Oct 23 '15 at 14:45
  • read the question again and try to find word 'performance'. The question is about quering realtionship, not about optimizing it – Vasily Liaskovsky Oct 24 '15 at 11:09
  • "so that it doesn't take a longer time.." This question is about optimizing retrieval time. – cosbor11 Oct 24 '15 at 11:13
0

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()
Vasily Liaskovsky
  • 2,248
  • 1
  • 17
  • 32