2

I receive a list of UserIds(about 1000 at a time) sorted by 'Income'. I have User records in "my system's database" but the 'Income' column is not there. I want to retrieve the Users from "my system's database" in the Sorted Order as received in the list. I tried doing the following using Active Record expecting that the records would be retrieved in the same order as in the Sorted List but it does not work.

//PSEUDO CODE
User.all(:conditions => {:id => [SORTED LIST]})

I found an answer to a similar question at the link below, but am not sure how to implement the suggested solution using Active Record.

ORDER BY the IN value list

Is there any other way to do it?

Please guide.

Shardul.

Community
  • 1
  • 1
SB.
  • 1,887
  • 2
  • 19
  • 31

2 Answers2

7

Your linked to answer provides exactly what you need, you just need to code it in Ruby in a flexible manner.

Something like this:

class User
  def self.find_as_sorted(ids)
    values = []
    ids.each_with_index do |id, index|
      values << "(#{id}, #{index + 1})"
    end
    relation = self.joins("JOIN (VALUES #{values.join(",")}) as x (id, ordering) ON #{table_name}.id = x.id")
    relation = relation.order('x.ordering')
    relation
  end
end

In fact you could easily put that in a module and mixin it into any ActiveRecord classes that need it, since it uses table_name and self its not implemented with any specific class names.

Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
  • How do you deal with `ids` being empty? I've tried this and it seems that in that case you get an error. – 8vius Nov 21 '14 at 19:05
0

MySQL users can do this via the FIELD function but Postgres lacks it. However this questions has work arounds: Simulating MySQL's ORDER BY FIELD() in Postgresql

Community
  • 1
  • 1
Cody Caughlan
  • 32,456
  • 5
  • 63
  • 68
  • Based on the answers in the link above, I would have to create a database function to achieve what I want. Am I right? I am somehow not very comfortable pushing anything to the database (a function or a procedure). Is there some other way to do it? – SB. Aug 18 '12 at 16:55