Weird Rails3 ActiveRecord results when using count and limit together

[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?

Best regards,
DBA

[Nicolás Sanguinetti]

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

Cheers,
-foca

> Best regards,
> DBA
>
> --
> You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to rubyonrails-co...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
>
>

[Jeremy Evans]

2010/7/31 Nicolás Sanguinetti <h...@nicolassanguinetti.info>:

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

[Wincent Colaiuta]

I think this ticket is related:

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

[DBA]

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

[Ken Collins]

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

[DBA]

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

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

[Mislav Marohnić]

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?

[Ken Collins]

Intuitive :)

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.

[DBA]

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

So, intuitive +1

[DBA]

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