Why does a query in rails with a limit not work unless .all is put on the end

Question

I have a query like this:

locations = Location.order('id ASC').limit(10)

which returns an array of 500 or so records - all the records in the table - i.e. the limit clause is being ignored.

Yet if I put a .all on the end:

locations = Location.order('id ASC').limit(10).all

it works and returns 10 records.

This code is being run in a rake task and I am using PostgreSQL if that makes any difference.

Why is it doing that? Surely the .all should not be required. What am I missing?

Answer 1

I think the behaviour depends on how you are handling the locations variable after setting it. This is because Location.order('id ASC').limit(10) isn't querying records but is returning an object of type ActiveRecord::Relation. The query will only occur once you call all, first, each, map, etc. on that object.

In my testing,

Location.order('id ASC').limit(10).map { |l| l.id }

returns an array of 10 ids as you would expect. But

Location.order('id ASC').limit(10).count

returns the total number of locations in the database, because it executes the SQL

SELECT COUNT(*) FROM "locations" LIMIT 10

which returns the full count of location rows (the limit is on the number of rows returned, not the count itself).

So if you are treating the result of Location.order('id ASC').limit(10) as an array by iterating through it, you should get the same result as if you had added all. If you are calling count, you will not. Kind of unfortunate, as I think ideally they should behave the same and you shouldn't have to know that you are dealing with an ActiveRecord::Relation instead of an array.

Answer 2

Ok here is my explanation First of all if you do Location.order('id ASC').limit(10).class you'll see ActiveRecord::Relation next on the site with rails API ActiveRecord::Relation doesn't have a method all however it includes ActiveRecord::FinderMethods and if you look there you'll find next

# File activerecord/lib/active_record/relation/finder_methods.rb, line 142
def all(*args)
  args.any? ? apply_finder_options(args.first).to_a : to_a
end

so it calls to_a method As was mentioned in the railscasts this method is defined as

def to_a  
  ...
  @records = eager_loading? ? find_with_associations : @klass.find_by_sql(arel.to_sql)  
  ...  
  @records  
end 

so it does SQL query on a third line with @records = eager_loading? ? find_with_associations : @klass.find_by_sql(arel.to_sql)