Using GORM efficiently to retrieve join data - Gra

问题:

I have User, and Follow Domain class that construct the Follower, Following relationship like Twitter. When my User visits another User's page, and click on their Follower list. I pop-up the list of the visited person's follower list, and then show a button in front of it that is labeled "Follow" or "Unfollow" depending on if you already following that person. So I do it the following way, but I'm not sure if this is efficient or maybe there is a better way of doing it. In order to make it efficient, I only retrieve 20 follower at a time and allow pagination.

> example of pop-up:
> ----------------------------------------------
> alex [follow]
> dave [unfollow]
> sarah[follow]
> paul [follow]
> ----------------------------------------------

Follow domain class has:

// The person to follow
User follow
// The person who is following the follow
User follower
// The status of the relationship if removed or still following
boolean status

User domain class is the typical Spring Security User class with a bunch of extra fields like locations and etc.

In my controller when I receive a request for follower list I do the following.

def visitedUser = User.get(visitedUserId)
// get the list of top 20 followers of this user
def followerList = Follow.findAllByFollow(visitedUser, [max:20])

// get the list of all people current user follow who is in the 20 follower list
def mutualFollow = Follow.findAllByFollowerAndFollowInList(currentUser, followerList)

Now I have the list of all the followers in profile of the person I'm visiting, and also the list of those who are mutual. Then I pass both to my GSP and while loop through the followerList in GSP, I compare the id of that follower, if the follower exist in my mutualFollow list I tweak the button to "unfollow" otherwise I keep the button as follow.

One step further to optimize this is to user projection to only retrieve the id of the mutualFollow instead of the whole USER domain object, but since USERs coming back are proxied and they have wrapper around them, I'm not sure that makes a big difference. Because All I retrieve is the id.

I appreciate any suggestion to improve this approach or an alternative. Thanks

回答1:

I decided to use HQL and only retrieve the necessary info of those USERs within the follow list, and then I did a join to get the mutual people they both follow. And used that in the GSP page. Here is the simplified solution in SQL:

SELECT Ur.follow_id, Urs.follow_id as mutual FROM (SELECT * FROM FOLLOW WHERE FOLLOWER_ID=1)as Urs
RIGHT JOIN (SELECT * FROM FOLLOW WHERE FOLLOWER_ID=3) as Ur
ON Urs.follow_id=Ur.follow_id order by Ur.follow_id;