CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4activerecord
Max Rose-Collins
Max Rose-Collins

Reputation: 1924

Ruby on Rails 4 count distinct with inner join

I have created a validation rule to limit the number of records a member can create.

class Engine < ActiveRecord::Base
  validates :engine_code, presence: true
  belongs_to :group
  delegate :member, to: :group

  validate :engines_within_limit, on: :create

  def engines_within_limit
    if self.member.engines(:reload).distinct.count(:engine_code) >= self.member.engine_limit
      errors.add(:engine, "Exceeded engine limit")
    end
  end
end

The above doesn't work, specifically this part,

self.member.engines(:reload).distinct.count(:engine_code)

The query it produces is

SELECT "engines".* 
FROM "engines" 
INNER JOIN "groups" 
ON "engines"."group_id" = "groups"."id" 
WHERE "groups"."member_id" = $1  [["member_id", 22]]

and returns the count 0 which is wrong

Whereas the following

Engine.distinct.count(:engine_code)

produces the query

SELECT DISTINCT COUNT(DISTINCT "engines"."engine_code") 
FROM "engines"

and returns 3 which is correct

What am I doing wrong? It is the same query just with a join?

Upvotes: 2

Views: 1411

Answers (2)

244an
244an

Reputation: 1589

AR:: means ActiveRecord:: below.

The reason for the "wrong" result in the question is that the collection association isn't used correct. A collection association (e.g. has_many) for a record is not a AR::Relation it's a AR::Associations::CollectionProxy. It's a sub class of AR::Relation, and e.g. distinct is overridden.

self.member.engines(:reload).distinct.count(:engine_code) will cause this to happen:

  • self.member.engines(:reload) is a AR::Associations::CollectionProxy

  • .distinct on that will first fire the db read, then do a .to_a on the result and then doing "it's own" distinct which is doing a uniq on the array of records regarding the id of the records.
    The result is an array.

  • .count(:engine_code) this is doing Array#count on the array which is returning 0 since no record in the array equals to the symbol :engine_code.

To get the correct result you should use the relation of the association proxy, .scope:
self.member.engines(:reload).scope.distinct.count(:engine_code)

I think it's a little bit confusing in Rails how collection associations is handled. Many of the "normal" methods for relations works as usual, e.g. this will work without using .scope:
self.member.engines(:reload).where('true').distinct.count(:engine_code)
that is because where isn't overridden by AR::Associations::CollectionProxy.
Perhaps it would be better to always have to use .scope when using the collection as a relation.

Upvotes: 1

Arup Rakshit
Arup Rakshit

Reputation: 118299

After doing long chat, we found the below query to work :

self.member
    .engines(:reload)
    .count("DISTINCT engine_code")

Upvotes: 3

Related Questions