Find model records by ID in the order the array of IDs were given

Multi tool use
I have a query to get the IDs of people in a particular order, say:
ids = [1, 3, 5, 9, 6, 2]
I then want to fetch those people by Person.find(ids)
But they are always fetched in numerical order, I know this by performing:
people = Person.find(ids).map(&:id)
=> [1, 2, 3, 5, 6, 9]
How can I run this query so that the order is the same as the order of the ids array?
I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.
I tried something like:
ids.each do |i|
person = people.where('id = ?', i)
But I don't think this works.
ruby-on-rails ruby-on-rails-3 activerecord
add a comment |
I have a query to get the IDs of people in a particular order, say:
ids = [1, 3, 5, 9, 6, 2]
I then want to fetch those people by Person.find(ids)
But they are always fetched in numerical order, I know this by performing:
people = Person.find(ids).map(&:id)
=> [1, 2, 3, 5, 6, 9]
How can I run this query so that the order is the same as the order of the ids array?
I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.
I tried something like:
ids.each do |i|
person = people.where('id = ?', i)
But I don't think this works.
ruby-on-rails ruby-on-rails-3 activerecord
add a comment |
I have a query to get the IDs of people in a particular order, say:
ids = [1, 3, 5, 9, 6, 2]
I then want to fetch those people by Person.find(ids)
But they are always fetched in numerical order, I know this by performing:
people = Person.find(ids).map(&:id)
=> [1, 2, 3, 5, 6, 9]
How can I run this query so that the order is the same as the order of the ids array?
I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.
I tried something like:
ids.each do |i|
person = people.where('id = ?', i)
But I don't think this works.
ruby-on-rails ruby-on-rails-3 activerecord
I have a query to get the IDs of people in a particular order, say:
ids = [1, 3, 5, 9, 6, 2]
I then want to fetch those people by Person.find(ids)
But they are always fetched in numerical order, I know this by performing:
people = Person.find(ids).map(&:id)
=> [1, 2, 3, 5, 6, 9]
How can I run this query so that the order is the same as the order of the ids array?
I made this task more difficult as I wanted to only perform the query to fetch people once, from the IDs given. So, performing multiple queries is out of the question.
I tried something like:
ids.each do |i|
person = people.where('id = ?', i)
But I don't think this works.
ruby-on-rails ruby-on-rails-3 activerecord
ruby-on-rails ruby-on-rails-3 activerecord
edited Aug 20 '12 at 7:56
lulalala
11.2k785148
11.2k785148
asked Apr 14 '12 at 1:07


JonathanJonathan
4,65473350
4,65473350
add a comment |
add a comment |
9 Answers
9
active
oldest
votes
Note on this code:
ids.each do |i|
person = people.where('id = ?', i)
There are two issues with it:
First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect
Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.
The first way would be by flattening:
ids.collect {|i| Person.where('id => ?', i) }.flatten
Even better version:
ids.collect {|i| Person.where(:id => i) }.flatten
A second way would by to simply do a find:
ids.collect {|i| Person.find(i) }
That's nice and simple
You'll find, however, that these all do a query for each iteration, so not very efficient.
I like Sergio's solution, but here's another I would have suggested:
people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }
I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
|
show 1 more comment
There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.
Solution one:
Person.find([1,2,3,4])
This will raise ActiveRecord::RecordNotFound
if no record exists
Solution two [Rails 3 only]:
Person.find_all_by_id([1,2,3,4])
This will not cause exception, simply return empty array if no record matches your query.
Based on your requirement choosing the method you would like to use above, then sorting them by given ids
ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}
Solution [Rails 4 only]:
I think Rails 4 offers a better solution.
# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')
# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
add a comment |
As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.
Mapping the IDs:
ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }
Note that this will cause multiple queries to be executed, which is potentially inefficient.
Sorting the result:
ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }
Or, expanding on Brian Underwoods answer:
ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)
Hope that helps
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
add a comment |
If you have ids
array then it is as simple as -
Person.where(id: ids).sort_by {|p| ids.index(p.id) }
OR
persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }]
ids.map {|i| persons[i] }
add a comment |
You can get users sorted by id asc
from the database and then rearrange them in the application any way you want. Check this out:
ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query
# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]
users.sort_by! {|u| ids.index u[:id]}
# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]
The trick here is sorting the array by an artificial value: index of object's id in another array.
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
Yeah, sure. The key line is the one withsort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third becauseids
array has 3 at third position.
– Sergio Tulentsev
Apr 14 '12 at 6:51
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
|
show 2 more comments
Old question, but the sorting can be done by ordering using the SQL FIELD
function. (Only tested this with MySQL.)
So in this case something like this should work:
Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)
Which results in the following SQL:
SELECT * FROM people
WHERE id IN (1, 3, 5, 9, 6, 2)
ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)
add a comment |
Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.
Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):
def self.order_by_id_list(id_list)
values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
.order("#{ self.table_name }_id_order.ordering")
end
The query solution is from this question.
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replaceLEFT JOIN
withJOIN
to match the original query so that this method works as expected.
– mrcasals
Nov 22 '18 at 9:14
add a comment |
If you are using MySQL, this might be a simple solution for you.
Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")
add a comment |
With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:
Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]
Note that using where
instead of find
does not preserve the order.
Person.where(country: "France").where([3, 2, 1]).map(&:id)
=> [1, 2, 3]
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10150152%2ffind-model-records-by-id-in-the-order-the-array-of-ids-were-given%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
9 Answers
9
active
oldest
votes
9 Answers
9
active
oldest
votes
active
oldest
votes
active
oldest
votes
Note on this code:
ids.each do |i|
person = people.where('id = ?', i)
There are two issues with it:
First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect
Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.
The first way would be by flattening:
ids.collect {|i| Person.where('id => ?', i) }.flatten
Even better version:
ids.collect {|i| Person.where(:id => i) }.flatten
A second way would by to simply do a find:
ids.collect {|i| Person.find(i) }
That's nice and simple
You'll find, however, that these all do a query for each iteration, so not very efficient.
I like Sergio's solution, but here's another I would have suggested:
people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }
I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
|
show 1 more comment
Note on this code:
ids.each do |i|
person = people.where('id = ?', i)
There are two issues with it:
First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect
Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.
The first way would be by flattening:
ids.collect {|i| Person.where('id => ?', i) }.flatten
Even better version:
ids.collect {|i| Person.where(:id => i) }.flatten
A second way would by to simply do a find:
ids.collect {|i| Person.find(i) }
That's nice and simple
You'll find, however, that these all do a query for each iteration, so not very efficient.
I like Sergio's solution, but here's another I would have suggested:
people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }
I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
|
show 1 more comment
Note on this code:
ids.each do |i|
person = people.where('id = ?', i)
There are two issues with it:
First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect
Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.
The first way would be by flattening:
ids.collect {|i| Person.where('id => ?', i) }.flatten
Even better version:
ids.collect {|i| Person.where(:id => i) }.flatten
A second way would by to simply do a find:
ids.collect {|i| Person.find(i) }
That's nice and simple
You'll find, however, that these all do a query for each iteration, so not very efficient.
I like Sergio's solution, but here's another I would have suggested:
people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }
I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)
Note on this code:
ids.each do |i|
person = people.where('id = ?', i)
There are two issues with it:
First, the #each method returns the array it iterated on, so you'd just get the ids back. What you want is a collect
Second, the where will return an Arel::Relation object, which in the end will evaluate as an array. So you'd end up with an array of arrays. You could fix two ways.
The first way would be by flattening:
ids.collect {|i| Person.where('id => ?', i) }.flatten
Even better version:
ids.collect {|i| Person.where(:id => i) }.flatten
A second way would by to simply do a find:
ids.collect {|i| Person.find(i) }
That's nice and simple
You'll find, however, that these all do a query for each iteration, so not very efficient.
I like Sergio's solution, but here's another I would have suggested:
people_by_id = Person.find(ids).index_by(&:id) # Gives you a hash indexed by ID
ids.collect {|id| people_by_id[id] }
I swear that I remember that ActiveRecord used to do this ID ordering for us. Maybe it went away with Arel ;)
answered Apr 14 '12 at 1:37
Brian UnderwoodBrian Underwood
9,89811427
9,89811427
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
|
show 1 more comment
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
1
1
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Just tried it in an old 2.3.8 project that I had and it turns out the find method doesn't order by the IDs automatically. I must be getting old... ;)
– Brian Underwood
Apr 14 '12 at 1:57
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
Could you explain your altered suggestion a little? At what point can this then be used to iterate through the new people result in the correct order? Or did you comment because it wasn't working? Thank you for your input though, I found it very useful
– Jonathan
Apr 14 '12 at 6:39
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
I've tried your solution and it's benchmarking slightly faster than any other solution here, thanks!
– Jonathan
Apr 14 '12 at 7:20
1
1
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
I probably should have been clearer that you can take the second line and assign it or further iterate upon it. I also probably should have started with my alternate solution, though I like starting from a bit of code and showing incremental changes until a more ideal solution is reached ;)
– Brian Underwood
Apr 15 '12 at 12:31
1
1
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
index_by is great but watch out if you receive your array filled with ids by HTTP. Spend nearly an hour to realise that ["1", "2", "3"] != [1, 2, 3].
– shadowhorst
Feb 13 '14 at 20:20
|
show 1 more comment
There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.
Solution one:
Person.find([1,2,3,4])
This will raise ActiveRecord::RecordNotFound
if no record exists
Solution two [Rails 3 only]:
Person.find_all_by_id([1,2,3,4])
This will not cause exception, simply return empty array if no record matches your query.
Based on your requirement choosing the method you would like to use above, then sorting them by given ids
ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}
Solution [Rails 4 only]:
I think Rails 4 offers a better solution.
# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')
# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
add a comment |
There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.
Solution one:
Person.find([1,2,3,4])
This will raise ActiveRecord::RecordNotFound
if no record exists
Solution two [Rails 3 only]:
Person.find_all_by_id([1,2,3,4])
This will not cause exception, simply return empty array if no record matches your query.
Based on your requirement choosing the method you would like to use above, then sorting them by given ids
ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}
Solution [Rails 4 only]:
I think Rails 4 offers a better solution.
# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')
# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
add a comment |
There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.
Solution one:
Person.find([1,2,3,4])
This will raise ActiveRecord::RecordNotFound
if no record exists
Solution two [Rails 3 only]:
Person.find_all_by_id([1,2,3,4])
This will not cause exception, simply return empty array if no record matches your query.
Based on your requirement choosing the method you would like to use above, then sorting them by given ids
ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}
Solution [Rails 4 only]:
I think Rails 4 offers a better solution.
# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')
# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load
There are two ways to get entries by given an array of ids. If you are working on Rails 4, dynamic method are deprecated, you need to look at the Rails 4 specific solution below.
Solution one:
Person.find([1,2,3,4])
This will raise ActiveRecord::RecordNotFound
if no record exists
Solution two [Rails 3 only]:
Person.find_all_by_id([1,2,3,4])
This will not cause exception, simply return empty array if no record matches your query.
Based on your requirement choosing the method you would like to use above, then sorting them by given ids
ids = [1,2,3,4]
people = Person.find_all_by_id(ids)
# alternatively: people = Person.find(ids)
ordered_people = ids.collect {|id| people.detect {|x| x.id == id}}
Solution [Rails 4 only]:
I think Rails 4 offers a better solution.
# without eager loading
Person.where(id: [1,2,3,4]).order('id DESC')
# with eager loading.
# Note that you can not call deprecated `all`
Person.where(id: [1,2,3,4]).order('id DESC').load
edited Oct 9 '13 at 10:27
answered Apr 14 '12 at 2:13
activarsactivars
1,6321215
1,6321215
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
add a comment |
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
Thank you, this definitely works and is a very valuable answer, thanks for contributing!
– Jonathan
Apr 14 '12 at 6:46
You are welcome!
– activars
Apr 14 '12 at 16:03
You are welcome!
– activars
Apr 14 '12 at 16:03
19
19
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
The Rails 4 solution doesn't answer the question. You are ordering by the numerical value of the id. That is how Rails does it by default.
– d_rail
May 21 '15 at 17:20
6
6
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
The question asked how to order ids in the way they were passed in... not by a default ASC or DESC
– courtsimas
Jul 14 '15 at 17:17
add a comment |
As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.
Mapping the IDs:
ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }
Note that this will cause multiple queries to be executed, which is potentially inefficient.
Sorting the result:
ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }
Or, expanding on Brian Underwoods answer:
ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)
Hope that helps
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
add a comment |
As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.
Mapping the IDs:
ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }
Note that this will cause multiple queries to be executed, which is potentially inefficient.
Sorting the result:
ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }
Or, expanding on Brian Underwoods answer:
ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)
Hope that helps
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
add a comment |
As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.
Mapping the IDs:
ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }
Note that this will cause multiple queries to be executed, which is potentially inefficient.
Sorting the result:
ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }
Or, expanding on Brian Underwoods answer:
ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)
Hope that helps
As I see it, you can either map the IDs or sort the result. For the latter, there already are solutions, though I find them inefficient.
Mapping the IDs:
ids = [1, 3, 5, 9, 6, 2]
people_in_order = ids.map { |id| Person.find(id) }
Note that this will cause multiple queries to be executed, which is potentially inefficient.
Sorting the result:
ids = [1, 3, 5, 9, 6, 2]
id_indices = Hash[ids.map.with_index { |id,idx| [id,idx] }] # requires ruby 1.8.7+
people_in_order = Person.find(ids).sort_by { |person| id_indices[person.id] }
Or, expanding on Brian Underwoods answer:
ids = [1, 3, 5, 9, 6, 2]
indexed_people = Person.find(ids).index_by(&:id) # I didn't know this method, TIL :)
people_in_order = indexed_people.values_at(*ids)
Hope that helps
answered Apr 14 '12 at 3:13
apeirosapeiros
1,4321311
1,4321311
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
add a comment |
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
Thank you, I tried your expanded answer which works - is this your preferred solution?
– Jonathan
Apr 14 '12 at 6:49
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
I haven't used index_by yet, but assuming it doesn't come with some inexplicably high performance penalty, then yes. It's quite concise in my opinion.
– apeiros
Apr 14 '12 at 8:33
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
Actually it seemed to be one of the faster solutions here - but each solution was very closely timed, I didn't test your first solution though - I'd have to take your word on whether or not you feel it would be more efficient
– Jonathan
Apr 14 '12 at 8:40
1
1
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
As I say in the answer, the first solution is probably quite slow due to the overhead of using multiple queries. For small number of ids, that shouldn't matter. But my preference is on the third.
– apeiros
Apr 14 '12 at 8:46
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
thank you,this is the perfect solution for me
– Jigar Bhatt
Nov 25 '14 at 6:59
add a comment |
If you have ids
array then it is as simple as -
Person.where(id: ids).sort_by {|p| ids.index(p.id) }
OR
persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }]
ids.map {|i| persons[i] }
add a comment |
If you have ids
array then it is as simple as -
Person.where(id: ids).sort_by {|p| ids.index(p.id) }
OR
persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }]
ids.map {|i| persons[i] }
add a comment |
If you have ids
array then it is as simple as -
Person.where(id: ids).sort_by {|p| ids.index(p.id) }
OR
persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }]
ids.map {|i| persons[i] }
If you have ids
array then it is as simple as -
Person.where(id: ids).sort_by {|p| ids.index(p.id) }
OR
persons = Hash[ Person.where(id: ids).map {|p| [p.id, p] }]
ids.map {|i| persons[i] }
answered Sep 4 '14 at 11:00
Sandip RansingSandip Ransing
5,40122641
5,40122641
add a comment |
add a comment |
You can get users sorted by id asc
from the database and then rearrange them in the application any way you want. Check this out:
ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query
# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]
users.sort_by! {|u| ids.index u[:id]}
# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]
The trick here is sorting the array by an artificial value: index of object's id in another array.
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
Yeah, sure. The key line is the one withsort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third becauseids
array has 3 at third position.
– Sergio Tulentsev
Apr 14 '12 at 6:51
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
|
show 2 more comments
You can get users sorted by id asc
from the database and then rearrange them in the application any way you want. Check this out:
ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query
# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]
users.sort_by! {|u| ids.index u[:id]}
# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]
The trick here is sorting the array by an artificial value: index of object's id in another array.
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
Yeah, sure. The key line is the one withsort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third becauseids
array has 3 at third position.
– Sergio Tulentsev
Apr 14 '12 at 6:51
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
|
show 2 more comments
You can get users sorted by id asc
from the database and then rearrange them in the application any way you want. Check this out:
ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query
# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]
users.sort_by! {|u| ids.index u[:id]}
# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]
The trick here is sorting the array by an artificial value: index of object's id in another array.
You can get users sorted by id asc
from the database and then rearrange them in the application any way you want. Check this out:
ids = [1, 3, 5, 9, 6, 2]
users = ids.sort.map {|i| {id: i}} # Or User.find(ids) or another query
# users sorted by id asc (from the query)
users # => [{:id=>1}, {:id=>2}, {:id=>3}, {:id=>5}, {:id=>6}, {:id=>9}]
users.sort_by! {|u| ids.index u[:id]}
# users sorted as you wanted
users # => [{:id=>1}, {:id=>3}, {:id=>5}, {:id=>9}, {:id=>6}, {:id=>2}]
The trick here is sorting the array by an artificial value: index of object's id in another array.
edited Apr 14 '12 at 7:09
answered Apr 14 '12 at 1:23
Sergio TulentsevSergio Tulentsev
180k30289304
180k30289304
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
Yeah, sure. The key line is the one withsort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third becauseids
array has 3 at third position.
– Sergio Tulentsev
Apr 14 '12 at 6:51
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
|
show 2 more comments
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
Yeah, sure. The key line is the one withsort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third becauseids
array has 3 at third position.
– Sergio Tulentsev
Apr 14 '12 at 6:51
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Just getting some sleep, will try this in the morning, thanks!
– Jonathan
Apr 14 '12 at 1:27
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
Could you elaborate on your answer for me please? I can see that it works but, not sure how to use it with a result of people, with names, emails, etc.
– Jonathan
Apr 14 '12 at 6:45
1
1
Yeah, sure. The key line is the one with
sort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third because ids
array has 3 at third position.– Sergio Tulentsev
Apr 14 '12 at 6:51
Yeah, sure. The key line is the one with
sort_by!
. You provide a block that is then called with an instance of user. You extract an id from this object, find position of this id in your original id array, and this position will also be position of user object in the sorted array. User with id=5 will come third because ids
array has 3 at third position.– Sergio Tulentsev
Apr 14 '12 at 6:51
1
1
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
Right before that. See my edit.
– Sergio Tulentsev
Apr 14 '12 at 7:09
1
1
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
No problem. Pick the best answer :)
– Sergio Tulentsev
Apr 14 '12 at 7:28
|
show 2 more comments
Old question, but the sorting can be done by ordering using the SQL FIELD
function. (Only tested this with MySQL.)
So in this case something like this should work:
Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)
Which results in the following SQL:
SELECT * FROM people
WHERE id IN (1, 3, 5, 9, 6, 2)
ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)
add a comment |
Old question, but the sorting can be done by ordering using the SQL FIELD
function. (Only tested this with MySQL.)
So in this case something like this should work:
Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)
Which results in the following SQL:
SELECT * FROM people
WHERE id IN (1, 3, 5, 9, 6, 2)
ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)
add a comment |
Old question, but the sorting can be done by ordering using the SQL FIELD
function. (Only tested this with MySQL.)
So in this case something like this should work:
Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)
Which results in the following SQL:
SELECT * FROM people
WHERE id IN (1, 3, 5, 9, 6, 2)
ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)
Old question, but the sorting can be done by ordering using the SQL FIELD
function. (Only tested this with MySQL.)
So in this case something like this should work:
Person.order(Person.send(:sanitize_sql_array, ['FIELD(id, ?)', ids])).find(ids)
Which results in the following SQL:
SELECT * FROM people
WHERE id IN (1, 3, 5, 9, 6, 2)
ORDER BY FIELD(id, 1, 3, 5, 9, 6, 2)
answered Jul 14 '16 at 15:35
Koen.Koen.
15.4k56270
15.4k56270
add a comment |
add a comment |
Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.
Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):
def self.order_by_id_list(id_list)
values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
.order("#{ self.table_name }_id_order.ordering")
end
The query solution is from this question.
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replaceLEFT JOIN
withJOIN
to match the original query so that this method works as expected.
– mrcasals
Nov 22 '18 at 9:14
add a comment |
Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.
Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):
def self.order_by_id_list(id_list)
values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
.order("#{ self.table_name }_id_order.ordering")
end
The query solution is from this question.
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replaceLEFT JOIN
withJOIN
to match the original query so that this method works as expected.
– mrcasals
Nov 22 '18 at 9:14
add a comment |
Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.
Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):
def self.order_by_id_list(id_list)
values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
.order("#{ self.table_name }_id_order.ordering")
end
The query solution is from this question.
Most of the other solutions don't allow you to further filter the resulting query, which is why I like Koen's answer.
Similar to that answer but for Postgres, I add this function to my ApplicationRecord (Rails 5+) or to any model (Rails 4):
def self.order_by_id_list(id_list)
values_clause = id_list.each_with_index.map{|id, i| "(#{id}, #{i})"}.join(", ")
joins("LEFT JOIN (VALUES #{ values_clause }) AS #{ self.table_name}_id_order(id, ordering) ON #{ self.table_name }.id = #{ self.table_name }_id_order.id")
.order("#{ self.table_name }_id_order.ordering")
end
The query solution is from this question.
edited May 23 '17 at 12:02
Community♦
11
11
answered Apr 5 '17 at 16:13
JerphJerph
3,15233538
3,15233538
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replaceLEFT JOIN
withJOIN
to match the original query so that this method works as expected.
– mrcasals
Nov 22 '18 at 9:14
add a comment |
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replaceLEFT JOIN
withJOIN
to match the original query so that this method works as expected.
– mrcasals
Nov 22 '18 at 9:14
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
Great answer! This is exactly what I was looking for, thanks! :)
– Szymon Rut
Nov 20 '17 at 9:08
You'll need to replace
LEFT JOIN
with JOIN
to match the original query so that this method works as expected.– mrcasals
Nov 22 '18 at 9:14
You'll need to replace
LEFT JOIN
with JOIN
to match the original query so that this method works as expected.– mrcasals
Nov 22 '18 at 9:14
add a comment |
If you are using MySQL, this might be a simple solution for you.
Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")
add a comment |
If you are using MySQL, this might be a simple solution for you.
Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")
add a comment |
If you are using MySQL, this might be a simple solution for you.
Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")
If you are using MySQL, this might be a simple solution for you.
Post.where(sky: 'blue').order("FIELD(sort_item_field_id, 2,5,1,7,3,4)")
answered Jun 18 '17 at 17:51


Eskim0Eskim0
324412
324412
add a comment |
add a comment |
With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:
Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]
Note that using where
instead of find
does not preserve the order.
Person.where(country: "France").where([3, 2, 1]).map(&:id)
=> [1, 2, 3]
add a comment |
With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:
Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]
Note that using where
instead of find
does not preserve the order.
Person.where(country: "France").where([3, 2, 1]).map(&:id)
=> [1, 2, 3]
add a comment |
With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:
Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]
Note that using where
instead of find
does not preserve the order.
Person.where(country: "France").where([3, 2, 1]).map(&:id)
=> [1, 2, 3]
With Rails 5, I've found that this approach works (with postgres, at least), even for scoped queries, useful for working with ElasticSearch:
Person.where(country: "France").find([3, 2, 1]).map(&:id)
=> [3, 2, 1]
Note that using where
instead of find
does not preserve the order.
Person.where(country: "France").where([3, 2, 1]).map(&:id)
=> [1, 2, 3]
answered Dec 29 '18 at 21:03
elliotcmelliotcm
63936
63936
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10150152%2ffind-model-records-by-id-in-the-order-the-array-of-ids-were-given%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Sk,OGEE6TmbyUfV6YSBvCuyLDxvVMAHAz6jxMJzTccsTTFKYF9AgWB0Zfel67LyB9JFRMhabNZVhbkT UkZEm ES1i