ActiveRecord Association select counts for included records

Question

Example

class User  
  has_many :tickets 
end

I want to create association which contains logic of count tickets of user and use it in includes (user has_one ticket_count)

Users.includes(:tickets_count)

I tried

  has_one :tickets_count, :select => "COUNT(*) as tickets_count,tickets.user_id " ,:class_name => 'Ticket', :group => "tickets.user_id", :readonly => true  

User.includes(:tickets_count)

 ArgumentError: Unknown key: group

In this case association query in include should use count with group by ... How can I implement this using rails?

Update

• I can't change table structure
• I want AR generate 1 query for collection of users with includes

Update2

I know SQL an I know how to select this with joins, but my question is now like "How to get data" . My question is about building association which I can use in includes. Thanks

Update3 I tried create association created like user has_one ticket_count , but

1. looks like has_one doesn't support association extensions
2. has_one doesn't support :group option
3. has_one doesn't support finder_sql

Bombazine

I do not have enough reputation to leave a comment but would just like to add a link to this post as a great reference for how to implement counter caching as suggested by djtal64 above: blog.appsignal.com/2018/06/19/activerecords-counter-cache.html However if you're using Rails 6 there is a fairly significant gotcha in that the whole object must be passed to the created one when updating it: dev.to/loribbaum/rails-5-2-x-rails-6-countercache-gotcha-3bgc – Sweetie

Answer 1

Try this:

class User

  has_one :tickets_count, :class_name => 'Ticket', 
    :select => "user_id, tickets_count",
    :finder_sql =>   '
        SELECT b.user_id, COUNT(*) tickets_count
        FROM   tickets b
        WHERE  b.user_id = #{id}
        GROUP BY b.user_id
     '
end

Edit:

It looks like the has_one association does not support the finder_sql option.

You can easily achieve what you want by using a combination of scope/class methods

class User < ActiveRecord::Base

  def self.include_ticket_counts
    joins(
     %{
       LEFT OUTER JOIN (
         SELECT b.user_id, COUNT(*) tickets_count
         FROM   tickets b
         GROUP BY b.user_id
       ) a ON a.user_id = users.id
     }
    ).select("users.*, COALESCE(a.tickets_count, 0) AS tickets_count")
  end    
end

Now

User.include_ticket_counts.where(:id => [1,2,3]).each do |user|
  p user.tickets_count 
end

This solution has performance implications if you have millions of rows in the tickets table. You should consider filtering the JOIN result set by providing WHERE to the inner query.

Presentday

rails 3.2 gives me ArgumentError: Unknown key: sql – Bombazine

has_one has next options [:class_name, :foreign_key, :select, :conditions, :include, :extend, :readonly, :validate, :remote, :dependent, :counter_cache, :primary_key, :inverse_of, :order, :as, :autosave] – Bombazine

@Fivell, updated the answer. I was supposed to use finder_sql instead of sql. I have updated my answer, take a look. – Presentday

I tried see my comment , finder_sql is not supported by has_one association. It gives me ArgumentError: Unknown key: finder_sql – Bombazine

intresting thanks. So you mean this is only one possible way ? How do you think why such simpe at first sight logic is not supported by associations ? – Bombazine

The has_one has always been a foster child among the associations. Even the API signature is inconsistent ( If you want proof, look at up votes I am gathering for this answer #2473482). IMHO, your case is better served by the 2nd solution I have suggested as it supports condition chaining and probably faster than using association(as the user data and the count is retrieved in one SQL). – Presentday

yes, your second solution works at least) as for now it's best answer! – Bombazine

how do you think if such associations will be supported in future? Is there some kind of roadmap for this somewhere? – Bombazine

I do not have any idea about the road-map. You might be better off declaring a new table to store all the stats for users and adding a has_one :stat association. Update the stat row when ever a user stat changes. This will improve stat query performance significantly. I know you you can't alter your current tables, may be you can introduce a new one? – Presentday

I have no such task, this question is just my curiosity =) – Bombazine

Very nice solution to avoid the disgusting hack that is :counter_cache. Only change I had to make (I'm using Postgres) is changing .select("users.*, a.tickets_count") to .select("users.*, COALESCE(a.tickets_count, 0) AS tickets_count") – Velma

Answer 2

You can simply use for a particular user:

user.tickets.count

Or if you want this value automatically cached by Rails.

Declare a counter_cache => true option in the other side of the association

class ticket
  belongs_to :user, :counter_cache => true
end

You also need a column in you user table named tickets_count. With this each time you add a new tickets to a user rails will update this column so when you ftech your user record you can simply accs this column to get the ticket count without additional query.

Inventor

If you cannot change table structure, then you just need to add a virtual method that returns the count in your User model: def tickets_count; @tickets_count ||= tickets.count; end and then you can call it as a normal attribute @user.tickets_count – Pteranodon

Answer 3

Not pretty, but it works:

users = User.joins("LEFT JOIN tickets ON users.id = tickets.user_id").select("users.*, count(tickets.id) as ticket_count").group("users.id")
users.first.ticket_count

Complainant

Tanks for response...but it doesn't work...what if user has no tickets? and how it groups ticket counts by user? Did you try it ? Also I want to use includes – Bombazine

Yes I tried it and it works fine for me. Why do you want to use includes? – Complainant

1) joins uses inner join, left join needed 2) where is group by user.id statement in our example?(needed for count) – Bombazine

yes of course now it's working... but it's not an answer for my question .. I'm looking a way to build association which I can use in includes method – Bombazine

Then I'm afraid I don't understand your question, sorry. – Complainant

Answer 4

What about adding a method in the User model that does the query?

You wouldn't be modifying the table structure, or you can't modify that either?

Nadabas

how I can call this method dynamicly with "includes" ? – Bombazine

Answer 5

How about adding a subselect scope to ApplicationRecord:

scope :subselect,
  lambda { |aggregate_fn, as:, from:|
    query = self.klass
      .select(aggregate_fn)
      .from("#{self.table_name} _#{self.table_name}")
      .where("_#{self.table_name}.id = #{self.table_name}.id")
      .joins(from)

    select("(#{query.to_sql}) AS #{as}")
  }

Then, one might use the following query:

users = User.select('users.*').subselect('COUNT(*)', as: :tickets_count, from: :tickets)

users.first.ticket_count
# => 5

Staffan