This project is archived and is in readonly mode.
ActiveRecord's "count" method doesn't work with has_many :through and MySQL
Reported by Mike Lapshin | March 16th, 2009 @ 10:10 AM | in 2.x
This bug appears in Rails 2.3.2 gem or Rails HEAD. When you trying to get counts of has_many :through association, filtered by named_scope, you get ActiveRecord::StatementInvalid exception. Example code:
class CommunityMembership < ActiveRecord::Base
belongs_to :user
belongs_to :community
end
class Community < ActiveRecord::Base
# Any named scope with any conditions
named_scope :foo, :conditions => ['communities.id != 0']
end
class User < ActiveRecord::Base
has_many :community_memberships
has_many :communities, :through => :community_memberships
end
>> User.find(:first).communities.foo.count
ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS count_communities_all FROM `communities` INNER JOIN `community_memberships' at line 1: SELECT count(`communities`.*) AS count_communities_all FROM `communities` INNER JOIN `community_memberships` ON `communities`.id = `community_memberships`.community_id WHERE (((communities.id > 5) AND ((`community_memberships`.user_id = 1))) AND ((`community_memberships`.user_id = 1)))
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/mysql_adapter.rb:320:in `execute'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/mysql_adapter.rb:595:in `select'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `select_one'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:19:in `select_value'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/calculations.rb:243:in `execute_simple_calculation'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/calculations.rb:134:in `calculate'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/calculations.rb:130:in `catch'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/calculations.rb:130:in `calculate'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/calculations.rb:48:in `count'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_collection.rb:176:in `count'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:2148:in `with_scope'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_collection.rb:176:in `send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_collection.rb:176:in `count'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:145:in `send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:145:in `send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/named_scope.rb:183:in `method_missing'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:2148:in `with_scope'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:206:in `send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:206:in `with_scope'
from (__DELEGATION__):2:in `__send__'
from (__DELEGATION__):2:in `with_scope'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/named_scope.rb:182:in `method_missing'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:2148:in `with_scope'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:206:in `send'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/associations/association_proxy.rb:206:in `with_scope'
from (__DELEGATION__):2:in `__send__'
from (__DELEGATION__):2:in `with_scope'
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/named_scope.rb:179:in `method_missing'
MySQL Server version 5.0.67.
Comments and changes to this ticket
-
Mike Lapshin March 16th, 2009 @ 10:12 AM
Just noticed that this bug appears in SQLite too. BTW, this code works fine in Rails 2.3.1.
-
Carlos Júnior (xjunior) March 16th, 2009 @ 01:35 PM
yeah, used to work on 2.3.1, but stoped again on 2.3.2
-
Manfred Stienstra March 16th, 2009 @ 03:11 PM
- State changed from “new” to “duplicate”
Duplicate of #2189.
-
csnk May 18th, 2011 @ 08:20 AM
- Importance changed from “” to “”
We are the professional jeans manufacturer, jeans supplier, jeans factory, custom jeans.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
Carlos Júnior (xjunior)
Manfred Stienstra