ActiveRecord Association select counts for include

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

Answer 1

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

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.

Answer 3

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.

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?