This project is archived and is in readonly mode.

#2250 ✓duplicate
Mike Lapshin

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

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>

Attachments

Pages