Searching from a Range of Ids in Activerecord

Searching from a range of ids in ActiveRecord

You can use the where method:

User.where(id: 14000..14500)

Explanation

The where method here receives a hash argument in its shortened form, where the value for the id key is a Range.

Range of IDs via ActiveRecord

What you need is limit and offset - read this for more info.

Example:

People.limit(200).offset(200)

The above code takes 200 records starting from 201st record - that means it would be records 201-400.

Rails - how to select all records by ID range

City.where(:id => start..stop).order('name ASC').select(:name)

Select items from database with id from x to y in rails

You can use where with ranges:

Model.where(id: 3..8) # 3..8 - your range

How to check if a number is within range in activerecord?

I think you need to use the PostgreSQL range operators and a bit of SQL in a string. In particular, you'd want the @> (contains element) operator:

JobRequirement.where('age @> ?', 20)

As far as supplying a range as a placeholder value goes:

JobRequirement.where('age <@ ?', 18..20)

you'll find that AR's knowledge of PostgreSQL's range types is somewhat limited. When you supply a range as a value for a placeholder, AR will want to expand the range to a comma delimited list as it assumes that you're saying something like where('col in (?)', 18..20) so you end up with nonsense like:

where age <@ 18,19,20

in the generated SQL. You can get around this by manually type casting the value; for example:

> ActiveRecord::Base.connection.type_cast(6..11)
 => "[6,11]" 
> ActiveRecord::Base.connection.type_cast(6...11)
 => "[6,11)" 

and then sending the string into the query where PostgreSQL should cast it to a PostgreSQL-range automatically:

JobRequirement.where('age <@ ?', ActiveRecord::Base.connection.type_cast(18..20))

Depending on where you're doing this, the connection method might be available with all the noise:

JobRequirement.where('age <@ ?', connection.type_cast(18..20))

And if PostgreSQL isn't picking the right version of the <@ operator on its own then you can help it with more typecasting:

JobRequirement.where('age <@ ?::int4range', connection.type_cast(18..20))

Rails ActiveRecord find array of ids, but some are not found

Wrong method. .find should be used to find specific records when you want the code to raise ActiveRecord::RecordNotFound. This is done to ensure that the record exists and avoid nil errors. Typically this is used to trigger a 404 response without repeating the same boilerplate:

def show
  @record = Record.find_by(id: params[:id])
  unless @record
    render file: 'public/404.html', status: :not_found
  end
end

Use .find_by(id: id) if you don't want the code to raise.

To fetch multiple records use where:

models = Model.where(id: [1,2,3])

If you want to determine which of those ids don't exist use an array diff:

bad_ids = [1,2,3] - models.ids

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

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 ;)

Rails Console find users by array of ids

For an array, you can use one of these:

# Will raise exception if any value not found
User.find( [1,3,5] )

# Will not raise an exception
User.find_all_by_id( [1,3,5] ) # Rails 3
User.where(id: [1,3,5])        # Rails 4

If you happen to be using a range, you can use these:

# Will raise exception if any value not found
User.find((1..4).to_a)   #same as User.find([1,2,3,4])

# Will not raise an exception
User.find_all_by_id(1..4)  # Rails 3
User.where(id: 1..4)       # Rails 4

As @diego.greyrobot notes in a comment, a range causes a SQL BETWEEN clause, whereas an array causes a SQL IN clause.

Don't use User.find_by_id() -- It will only return one record, no matter how may IDs you pass in.

---

Related Topics