Order a set of results by an externally calculated score

Question

I have a site that fetches the ID and SCORE of records for a user from an external source.

I want to then fetch the records that match the ID's, ordered by each ID's SCORE.

The ID's and SCORES come to the app as XML which I'm currently parsing into an array (of hashes):

[{90=>279}, {32=>400}]

How can I use that score to order the records returned?

Try to be more specific and give examples of how you've tried to solve this. — gernberg, Aug 25 '15 at 15:21
Yes, i know that's the usual thing but sometimes the question is "where the hell do i even start." — Will, Aug 25 '15 at 15:35
XML, which is easy to parse, so i end up with an array of ID's & Scores. — Will, Aug 25 '15 at 15:41
Can you add (in an edit to your question) an example "array of ID's & Scores" please? — Max Williams, Aug 25 '15 at 15:56

score 1 · Accepted Answer · edited May 23 '17 at 12:06

src_data = [{90=>279}, {32=>400}] 
ids = src_data.sort_by{|hash| hash.values.first}.reverse.collect(&:keys).flatten   
# => [32,90]

ids should now be an array of ids, in order of highest score to lowest score. Now we can do the find.

@users = User.where("id in (?)", ids).order("ORDER BY FIELD(ID,#{ids.join(',')})")

This should generate sql like

select * from users where id in (32,90) ORDER BY FIELD(ID,32,90);

which should give you the users back in the right order.

EDIT - for postgres

Postgresql doesn't have the field() function. You can define your own, see Simulating MySQL's ORDER BY FIELD() in Postgresql, but you might prefer to not bother and just reorder the results after you get them:

@users = User.where("id in (?)", ids).sort_by{|user| ids.index(user.id)}

thanks max. This got me through and is easily extendable when I need to manipulate the score or whatever. — Will, Aug 26 '15 at 14:04

gernberg · Answer 2 · 2015-08-25T16:17:39.093

0

If it's a standard ruby array you should be able to sort it by using "sort_by".

Two examples of sorting based on the keys / values:

results = [{90=>279}, {32=>400}] 

results.sort_by{|result| result.keys[0]}
# => [{32=>400}, {90=>279}] 

results.sort_by{|result| result.values[0]}
# => [{90=>279}, {32=>400}]

Check this blogpost on more information regarding sort_by and sort in Ruby: http://brandon.dimcheff.com/2009/11/18/rubys-sort-vs-sort-by.html

edited Aug 25 '15 at 16:17

answered Aug 25 '15 at 15:46

gernberg

2,587
17
21

Right, sorry I see where the confusion is. I want to fetch the records that match the ID's ordered by the SCORE's. I've updated the question – Will Aug 25 '15 at 15:50
@Will Is that more what you are looking for? – gernberg Aug 25 '15 at 16:17

Order a set of results by an externally calculated score

2 Answers2