Rails database - optimising code for specific database request

Question

I have a database (PG) of users, each of which belong to an organisation.

And then items, each of which belong to a user.

Each item has 'points' associated with it.

I am looking to find the top 3 users with the most points scored over the last 7 days...

Here is what I have got thus far - but I think it is very slow - any better ideas?

organisation_users = User.where(:organisation => current_user.organisation)


    total_data = []
    organisation_users.each do |user|
        points = Item.where("date > ?", Time.new - (7*86400)).where(:user_id => user.id).map{ |h| h.points.to_f}.sum
        user_data = [user.id, points]
        total_data = total_data.push(user_data)
    end

    list_of_points = total_data.map{ |h| h[1]}
    highest_points = list_of_points.sort[-1]
    highest_points_user_id = total_data[list_of_points.find_index(highest_points)][0]

And then do the same for the second and the third highest...

I realise there are probably a lot of ways to speed this up so any feedback would be great.

Baldrick · Accepted Answer · 2016-02-26 15:19:27Z

1

You should be able to do it in a single SQL request using group . Something like :

top3 = Item.
  select('sum(points) as total_points, users.id')
  joins(:user).
  where(users: {organisation: current_user.organisation}).
  group('users.id').
  order('total_points').
  limit(3)

It returns an array with the id of the users, and their total points.

edited Feb 26, 2016 at 15:19

answered Feb 26, 2016 at 11:14

Baldrick

24.4k6 gold badges76 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jmohey Over a year ago

Should have mentioned in question (now edited) - using a Postgres DB. Should this still work?

Jmohey Over a year ago

"ActiveRecord::ConfigurationError Exception: Association named 'users' was not found on Item" is what it comes up with..?

Baldrick Over a year ago

Fixed with joins(:user)

Jmohey Over a year ago

I can't seem to get it to work... The error now is : " PG::UndefinedFunction: ERROR: function sum(character varying) does not exist"

Baldrick Over a year ago

Looks like the points column is not a numeric column, but a string... :-/

|

Collectives™ on Stack Overflow

Rails database - optimising code for specific database request

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related