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

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.