Rails postgres order by custom

Question

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

This question has already been anwsered here : stackoverflow.com/questions/866465/… — Jef
– Jef, Commented Jan 9, 2013 at 10:28
Not quite a duplicate as this is Rails-specific and I'd like to achieve this with ActiveRecord if possible — jay
– jay, Commented Jan 9, 2013 at 10:33

Chowlett · Accepted Answer · 2013-01-09 10:46:06Z

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.

edited Jan 9, 2013 at 10:46

answered Jan 9, 2013 at 10:32

Chowlett

47k21 gold badges119 silver badges153 bronze badges

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

3 Comments

jay Over a year ago

Thanks, I have already thought of something similar but I would prefer to do it with AR if possible.

Chowlett Over a year ago

What do you mean? That is using AR.

jay Over a year ago

You are correct, sorry. I ended up using stackoverflow.com/questions/12012574/…

bonCodigo · Accepted Answer · 2013-01-09 10:28:07Z

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

answered Jan 9, 2013 at 10:28

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Collectives™ on Stack Overflow

Rails postgres order by custom

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related