1

I am searching for records using an IN query likes so:

Event.where("events.id IN (#{events.nil? ? '' : events.collect(&:id).join(',')})")

Which produces output like:

SELECT "events".* FROM "events" WHERE (events.id IN (143,169,139,48,172,146,145,124,111,49,108,18,113,144)) AND (status = 'live') LIMIT 10

However, this is then sorted by update_at by default. I'd like it to sort by the IN part of the query. What I mean is that I'd like this to be the order it returns in:

143,169,139,48,172,146,145,124,111,49,108,18,113,144

Is that possible at all?

I must also note that I can't add a priority column or similar to the table as the "events" part will be dynamically generated.

I've just discovered a very similar question here:

Postgres ORDER BY values in IN list using Rails Active Record

Feel free to mark this one of mine as duplicate

Community
  • 1
  • 1
jay
  • 305
  • 1
  • 3
  • 12

2 Answers2

2

This is Rails, so use Rails' methods for database querying. I believe:

Event.where(:id => events.map(&:id)).order(:id)

is what you're looking for.

If events is already a collection of database records, then just .where(:id => events) will also work.

Chowlett
  • 45,935
  • 20
  • 116
  • 150
  • Thanks, I have already thought of something similar but I would prefer to do it with AR if possible. – jay Jan 09 '13 at 10:35
  • What do you mean? That _is_ using AR. – Chowlett Jan 09 '13 at 10:35
  • You are correct, sorry. I ended up using http://stackoverflow.com/questions/12012574/postgres-order-by-values-in-in-list-using-rails-active-record – jay Jan 09 '13 at 10:39
0

Have you tried order by?

SELECT "events".* FROM "events" 
WHERE (events.id IN (143,169,139,48,172,146,145,124,111,49,108,18,113,144)) 
AND (status = 'live') 
ORDER BY events.id asc
LIMIT 10
bonCodigo
  • 14,268
  • 1
  • 48
  • 91