Postgres ORDER BY values in IN list using Rails Active Record

Question

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.

Cody Caughlan · Accepted Answer · 2012-08-22 19:48:36Z

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.

answered Aug 22, 2012 at 19:48

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

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

1 Comment

8vius Over a year ago

How do you deal with ids being empty? I've tried this and it seems that in that case you get an error.

Community · Accepted Answer · 2017-05-23 11:44:29Z

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

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Aug 17, 2012 at 20:15

Cody Caughlan

32.8k5 gold badges67 silver badges68 bronze badges

1 Comment

SB. Over a year ago

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?

Collectives™ on Stack Overflow

Postgres ORDER BY values in IN list using Rails Active Record

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related