ActiveRecord Association select counts for included records

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.

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

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.