Weird Rails3 ActiveRecord results when using count and limit together

I've implemented the following scope in a rails 3 application:

    scope :popular, lambda { |l = 5| order('views desc').limit(l) }

However, it seems that when you attempt to count its records directly it doesn't apply the scope filters.

For example:

    Post.popular.size #=> 20

Checking the log, it executes the following query:

    SQL (0.4ms) SELECT COUNT(*) AS count_id FROM `posts` LIMIT 5

Now if I execute

    Post.popular.all.size #=> 5

And the correct query is executed:

    Post Load (1.5ms) SELECT `posts`.* FROM `posts` ORDER BY views desc LIMIT 5

Anyone else experienced this kind of behavior? If so, any idea if this is the expected behavior or am I facing a bug?

Best regards, DBA

I've implemented the following scope in a rails 3 application:

scope :popular, lambda { |l = 5| order('views desc').limit(l) }

However, it seems that when you attempt to count its records directly it doesn't apply the scope filters.

For example:

Post.popular.size #=> 20

Checking the log, it executes the following query:

SQL (0.4ms) SELECT COUNT(*) AS count_id FROM `posts` LIMIT 5

Now if I execute

Post.popular.all.size #=> 5

And the correct query is executed:

Post Load (1.5ms) SELECT `posts`.* FROM `posts` ORDER BY views desc LIMIT 5

Anyone else experienced this kind of behavior? If so, any idea if this is the expected behavior or am I facing a bug?

This is expected. The three ways to get the size of an ActiveRecord collection are:

.count #=> this always triggers a SELECT COUNT(*) on the database .size #=> if the collection has been loaded, defers to Enumerable#size, else does the SELECT COUNT(*) .length #=> always loads the collection and then defers to Enumerable#size (I think it's length, but I don't think I ever used it :P)

So, since Post.popular doesn't trigger the query and load the objects, calling size will do a count query.

This is, apparently, a good use case for Post.popular.length :slight_smile:

Cheers, -foca

Or a good reason to change the code to do:

    SELECT COUNT(*) FROM (SELECT `posts`.* FROM `posts` ORDER BY views desc LIMIT 5) AS some_alias

which is Sequel's behavior.

Jeremy

I think this ticket is related:

https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/5060

Hello and thanks for the feedback.

foca, thanks for the clarification. I did know about the differences between .size and .count but I didn't remember about .length.

However, despite length returning the needed results (potentially with an extra query), I find it misleading to have AR execute a COUNT query on both .count and .size that ignores the current scope, deliberately expressed by the developer.

In that regard, I think Jeremy is right appointed Sequel behavior is more in line with what the average developer - like myself - would expect AR to return.

I'm not too familiar with AR codebase, but I'll try and check how hard it would be to modify .count and .size, making them take into account the current scope.

Thanks, DBA

DBA,

I'm not sure that would be the right things to do. The #count/#size/#length has been a documented feature of AR for as long as I can remember changing it on any scope does not sound like a good idea to me.

- Ken

Hello Ken,

Even though I understand, and agree to an extent with your point, I don't think developers should be mislead. Current behavior:

Model.all.count #=> should indeed execute a count on all records Model.all.size #=> if the records are not loaded it should return the collection size, otherwise fallback to count Model.all.length #=> forces the load and counts the collection

What I'm suggesting is the following change:

Model.all.count #=> no changes Model.<some limitation / scope>.count #=> compute the limitation clauses (eg where) and execute a count statement based on those limitators Model.all.size #=> no changes Model.<some limitation / scope>.size #=> if the records are not loaded it should return the collection size, otherwise fallback to count described above Model.all.length #=> no changes Model.<some limitation / scope>.length #=> no changes

What you guys think of this behavior?

Even though the changed behavior of count and size _will not_ be backwards compatible if you are relying on it to do a full table count statement, it will be more precise and less ambiguous. Doing a full table count when you're using limitations is, in my honest opinion, misleading and should be considered a bug.

I'm currently looking at ActiveRecord::Relation#arel to try and figure out the best way to compute the current limitators (eg where) and issue an arel count based on them. If anyone has any pointers it would be of great assistance :slight_smile:

Since any change of the current behavior would potentially be backwards incompatible, it would be more helpful to test these changes as a plugin/gem and see how it goes. Please let me know if you have any pointers on the best way to get arel to perform this "scoped" count.

Best regards, DBA

In my opinion, this should never return a value larger than 5:

Model.where(blah blah).order(blah blah).limit(5).count

Intuitive? Counter-intuitive?

Intuitive :slight_smile:

Funny you should mention this. I just finished up the parts of the SqlserverCompiler that deal with taking the limit/offset (taken/skipped) and wrapping up all the clauses for a lean count. Basically passing a few tests the adapter was failing in RelationTest. I added these just to make sure I was in parity with the MysqlCompiler.

http://github.com/rails-sqlserver/activerecord-sqlserver-adapter/commit/d295b2e18cb1581fc67be57756a82d26642952fd#L2R59

Notice how I use #size because I know from the ActiveRecord docs and tests that this issues a count anyway.

That's exactly what I was suggesting as the behavior of ActiveRecord::Relation.Count :slight_smile:

So, intuitive +1

Thanks for sharing the link Ken. Certainly helped me know where to look first.

I checked Arel's GenericCompiler#select_sql and noticed that it already implements the "scoped" .count & .size behavior, much like your SQL Server specific counterpart. However, for some reason, when using Rails 3.0.0.rc with Arel 0.4.0 it returns the values I've mentioned in my initial post. Odd, considering that it apparently falls back to arel's MySqlCompiler, which in turn will rely on GenericCompiler#select_sql. Probably missing something there.

Unfortunately I also appear to be missing the reason why, when playing with the GenericCompiler's code (http://gist.github.com/507110), my monkey patched version has has the value of self is set to Arel::Project, forcing me not only to redefine build_clauses but also to use #send on the methods such as select_clauses.

Regardless of that, after checking your code, I could get both count and size to return the expected value: 5. Furthermore, I've made ActiveRecord::Calculations#execute_simple_calculation only drop the :order clause should the adapter be set to PostgreSQL.

All this bring up three questions 1) where is GenericCompiler#select_sql being redefined / altered in a way that prevents it from executing the "scoped" count? 2) what would be the best way to get around the need to redefine build_clauses and use the #send method on relation? 3) any foreseeable impact in the change to ActiveRecord::Calculations#execute_simple_calculation?

Best regards, DBA