1

I am trying to retrieve a collection of data by an order like this:

@all_data= Data.find(@data_ids)

@data_ids - have the ids of the data to be retrieved. For instance:

Data

ID Name
1   A   
2   B  
3   C  
4   D

If the @data_ids are like [3,2,4] I want to retrieve the data in that order, sou it would be C, B, D... The thing is it always retrieve the data in B, C, D order. Is that possible to do? To ignore that order and to retrieve it by the given params order?

The thing is,

I have two tables, table A and table Data. 

Table A: 
Relation, Data_ID, ORDER   
1; 1; 2;  
1; 2; 1;    
2; 3; 3;

So what I want to do is to retrieve data_id by order from the relation one I will have (2,1), and I am doing it, but when I find (2,1) I receive 1,2.

heisenberg
  • 1,172
  • 6
  • 14
  • 31
  • 2
    You are not actually guaranteed any particular order unless you specify one with an `.order` clause. Is the data sorted in some other field in a way that would lend itself to that? – David Hoelzer Dec 14 '15 at 23:49
  • 1
    Search harder, I know I've seen this question several times around here. Short answer: do it yourself using a big ugly `CASE id WHEN...` mess in an ORDER BY or do it yourself in Ruby using the usual "sort an array based on another array" techniques. – mu is too short Dec 14 '15 at 23:52
  • @muistooshort a case would not be possible since the number of data to retrieve changes. – heisenberg Dec 15 '15 at 00:01
  • @DavidHoelzer I updated the question, so I think you can understand my goal a little better now – heisenberg Dec 15 '15 at 00:02
  • That didn't help. I'm sorry, I can't make out what you're saying there. Try for simpler sentences. :) – David Hoelzer Dec 15 '15 at 00:04
  • A CASE is possible. You can pass a little bit of SQL to `.order` as a string and Ruby is quite capable of building such a string. – mu is too short Dec 15 '15 at 02:16

2 Answers2

2

In MySQL there is a function that gives you the power to override custom ordering - take a look at "Sort by specific ids in ActiveRecord".

If you are using Postgres, you may consider this approach (credit to Omar Qureshi for his answer in "ActiveRecord.find(array_of_ids), preserving order":

unsorted = Model.find(arr)
sorted = arr.inject([]){|res, val| res << unsorted.detect {|u| u.id == val}}
Community
  • 1
  • 1
Rafael Bizarra
  • 103
  • 10
  • 1
    Instead of saying something nebulous like "take a look here", use more descriptive anchor text so people have a clue what they're clicking on. Stack Overflow makes it really easy, simply paste the full URL for that page into the text and it'll do the rest. See "[Don't use "click here" as link text](http://www.w3.org/QA/Tips/noClickHere)" and "[Link text](http://www.w3.org/TR/WCAG10-HTML-TECHS/#link-text)" – the Tin Man Dec 15 '15 at 00:22
  • @theTinMan appreciate the feedback, will use for future questions/replies. – Rafael Bizarra Dec 15 '15 at 13:19
0

Just fetch records from database, and sort them in memory. This works when what you wanna fetch is relatively a small amount.

Data.find(@data_ids).sort_by!{|data| @data_ids.index(data.id)}

P.S. the reason why you always get the data in id's order is that primary keys are always indexed, and the database traverses indexes in some order (asc or desc).

Aetherus
  • 8,720
  • 1
  • 22
  • 36
  • 3
    Please explain why that works. Tossing out code helps once; Explaining why the code works helps in the future. – the Tin Man Dec 15 '15 at 00:26
  • The "primary keys are always indexed, and the database traverses indexes in some order (asc or desc)" part isn't quite true. They can come out in any order that happens to be convenient for the database whether or not a PK is involved. Any order you see is purely accidental and cannot be depended on unless an ORDER BY is present. – mu is too short Dec 15 '15 at 02:18