Issue 39 - local neighborhood

[abacha]

I thought about defining the range of neighboors (fixed 9 in the code) on a constant or maybe a default parameter to the method, I'll refactor that later but I'd like some opinion on my approach to the problem

[abacha]

I think I`ve managed the window function to calculate the ranks (https://gist.github.com/1680844) how do you suggest that I retrieve the surrounding users? is it possible to do it in only one query?

[jordanbyron]

Hey Adriano,

Rather than guessing if your code works, why not implement it in the application and run the existing tests to prove it works. Just looking at the gist you posted I don't think that query is returning the correct results. Look at the existing leaderboard code:

# For each user, we need a count of correct submissions, along with
# the date of the most recent correct submission.

solutions = Submission.correct.
  select('user_id, MAX(created_at) AS latest_solution, COUNT(*) AS solved').
  group('user_id')

# We want to sort results by:
#   1) highest number of correct scores
#   2) earliest creation date of the *last* correct submission
#
# This inner join is a bit fugly, but it allows us to gather all
# the data and use it both for sorting and for displaying with
# one database query.

User.select("users.*, solved, latest_solution").
  joins("INNER JOIN (#{solutions.to_sql}) q1 on q1.user_id = users.id").
  order("solved DESC", "latest_solution").
  limit(limit).eligible_for_display

Notice that in the first query we only look for correct submissions. That's something the new query doesn't do. You probably just need to edit the User.select parameter to include the rank function. But we have tests for the leaderboard so be sure to use them. They'll confirm if your new solution works.

How do you suggest that I retrieve the surrounding users?

I think that can be done in two steps:

1. Get the current user's rank, by running the leaderboard query with WHERE user_id = #{current_user.id}
2. Then run the leaderboard query one more time but this time selecting records between current_user_rank + 5 and current_user_rank - 5

[abacha]

1. Get the current user's rank, by running the leaderboard query with WHERE user_id = #{current_user.id} if I ran the leaderboard query with the user_id where clause, the rank will always return '1', cause it will return just 1 result, right? and when you say "run the leaderboard query" you mean this solutions = Submission.correct. select('user_id, MAX(created_at) AS latest_solution, COUNT(*) AS solved'). group('user_id')

with the rank function?

my last gist wasn't fully functional, I've made it just to check with you the rank function, cause I have zero experience with postgree and almost none with rails. I know I'm working against the clock here but I had some problems this weekend and didn't have time to do anything. I'll to my best to finish this on time

[jordanbyron]

Get the current user's rank, by running the leaderboard query with WHERE user_id = #{current_user.id} if I ran the leaderboard query with the user_id where clause, the rank will always return '1', cause it will return just 1 result, right?

You need to create a subquery and then pick the user_id out of that. For example if I wanted to lookup a user.id == 12:

SELECT name, rank, id FROM 
  (SELECT name, id, rank() OVER (ORDER BY created_at) FROM USERS) AS leaderboard 
WHERE leaderboard.id = 12;

That would return the correct rank for the user. Obviously that is just a simplified example and doesn't return the correct rank. But you should be able to use the existing leaderboard logic in conjunction with the rank function to get that information.

and when you say "run the leaderboard query" you mean this

For the leaderboard query I was referring to the existing User.leaderboard code I mentioned above. If you want to see what that looks like in SQL just call to_sql on the result.

I'm trying to do my best to help you out without just writing the code myself. I think you have all the tools and examples here to finish this feature. Don't forget once we finish this part you also need to wire up the UI. Luckily you already have a mock to work from so that should help. Let me know if there is anything else I can do or if something here wasn't clear.

:smile:

[jordanbyron]

Adriano I'm sorry we can't seem to make any progress on this patch. I knew this would be tricky but I was hoping it would be a little easier than this :sweat:

Luckily I have a proof of concept version working on my machine. So I know it can be done :wink2: Let me know if you need some more guidance. Thanks for continuing to push along!

[abacha]

I'm sorry too, I thought I'd have more time to work on it but my weekend was totally off. I'm working on it till I get it done, don't give up on me lol :P

[abacha]

am I getting closer?

[abacha]

I found some bugs on my last commit, I'm working on them now.. maybe I'll only be pushing again in the morning

[abacha]

hey Jordan, would you please check my last commits?

[jordanbyron]

:tada: :balloon: :smile: :balloon:

YAYAYAYA! Great work @abacha this is almost exactly what I had in mind. Thanks for pushing hard to finish this task up.

I did make some small changes. Check out my commits to see what I did:

4f14b32dd6ad0da124fc5b1104b813ab6f5a164e 1546761527468ea97380a6b654d99707fe79aa6d

Thanks again!!!

:cake:

Jordan

[abacha]

:D thanks for your support! I'm glad I could help