Oyvind Kvanes Oyvind Kvanes - 2 years ago 70
SQL Question

Sorting users in Rails based on values in array attribute of user model

I have a User model which has an array inside of it. This array is used to store points the user has scored in various activities. It basically looks like this:

<ActiveRecord::Relation [#<User id: 1, fullname: "Kaja Sunniva Edvardsen", points: [0, 4170, 3860, 2504, 2971, 3859, 4346]>, #<User id: 2, fullname: "Alexander Lie Sr.", points: [0, 3273, 3681, 2297, 2748, 4202, 3477]>]>

I want to sort all Users by the different values in the points array to be able to create ranking list for each of the different activities, points[0], points[1], etc...

Sorting by points[1] should return Kaja first, 4170>3273, sorting by points[6] should put Alexander first, 4202>3859

How do I do this?

Answer Source

As far as I know, MySQL does not have an integrated array type.

Assuming you have a model like this:

class User < ActiveRecord::Base
  # ...
  serialize :points, Array
  # ...

You cannot sort with order queries, but you can try another solution (less efficient), handling the resources as an array:

User.all.sort { |user1, user2| user2.points[1] <=> user1.points[1] }

Which will return an array instead of an ActiveRecord query. Also, bear in mind that this code will not handle nil values (i.e. What if an user only have 2 elements in points?).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download