Count objects after using activerecord joins

Question

I just noticed that using .size or .count on a query that has joins gives unexpected results.

User.some_scope.size #=> 10
User.joins(:associations).some_scope.size #=> 10 + associated object count

How do I get the count of Users that fit the scope no matter what I join onto the table?

Some of the actual code:

class User < ActiveRecord::Base
  has_many :tracking_logs, class_name: "Tracking::Log", dependent: :destroy
end

class Tracking::Log < ActiveRecord::Base
  belongs_to :user
end

User.where(created_at: Time.now-1.day..Time.now).size #=> 18
SQL: SELECT COUNT(*) FROM "users"  WHERE ("users"."created_at" BETWEEN '2015-05-25 10:40:11.423142' AND '2015-05-26 10:40:11.423741')
User.joins(:tracking_logs).where(created_at: Time.now-1.day..Time.now).size #=> 3188
SQL: SELECT COUNT(*) FROM "users" INNER JOIN "tracking_logs" ON "tracking_logs"."user_id" = "users"."id" WHERE ("users"."created_at" BETWEEN '2015-05-25 10:41:15.260113' AND '2015-05-26 10:41:15.260684')

I expect .size to be applied only to the base model I call the query on, anything else is just confusing.

NB, being explicit about which model's created_at to check does not change the result e.g.

User.joins(:tracking_logs).where( users: { created_at: Time.now-1.day..Time.now } ).size #=> 3188
SQL: SELECT COUNT(*) FROM "users" INNER JOIN "tracking_logs" ON "tracking_logs"."user_id" = "users"."id" WHERE ("users"."created_at" BETWEEN '2015-05-25 10:49:29.485508' AND '2015-05-26 10:49:29.486120')

User attributes:
               :id => :integer,
         :category => :string,
            :email => :string,
       :created_at => :datetime,
       :updated_at => :datetime,
           :locale => :string,
   :original_email => :text,
  :registration_id => :integer,
       :first_name => :text,
      :login_count => :integer,
       :offer_type => :text,
              :cid => :string,
   :fb_like_status => :integer,
:product_purchases => :text

Tracking::Log attributes:
                  :id => :integer,
             :user_id => :integer,
 :tracking_referer_id => :integer,
                  :to => :string,
     :controller_name => :string,
         :action_name => :string,
:tracking_activity_id => :integer,
 :time_since_last_log => :integer,
          :created_at => :datetime,
          :updated_at => :datetime

Can you provide some of your code. Like the models and data on which it happens? I cannot seem to reproduce the issue. — IngoAlbers
Same here. Could you try turning on logging with ActiveRecord::Base.logger = Logger.new(STDOUT) and showing SQL that gets generated for the first and the second query? — SkyWriter
which created at you want to compare users or tracking_logs? — Vrushali Pawar
Users'. Looking at the query I am wondering how activerecord resolves the collision of attribute names. Would being explicit solve this? — Epigene

IngoAlbers IngoAlbers · Accepted Answer · 2015-05-26T12:39:17

The problem here is that the joins will return the same user multiple times, if the user has more than one tracking log. You can use uniq to get each user only once:

User.joins(:tracking_logs).where(created_at: Time.now-1.day..Time.now).uniq.size

See also here: http://guides.rubyonrails.org/active_record_querying.html#joining-a-single-association

Count objects after using activerecord joins

1 Answers