Q:

What is the best syntax for using a `where` statement to find association counts

I can think of a few ways to do this, but I'm unsure what to choose..
I have the class Topic and I am trying to scope this so that I only return Topics if it has the associated object Reply or topic.replies as a count greater than 0.
Worst way to do this :
@topics.select{ | topic | topic.replies > 0 && topic.title == "Conversation" }

Ideally, I'd like to use a where scope.
  scope = current_user.topics
  scope = scope.joins 'left outer join users on topics.registration_id = registration_members.registration_id'
  # scope = .. here I want to exclude any of these topics that have both the title "Conversations" and replies that are not greater than 0

I need to "append" these selections to anything else already selected. So my selection shouldn't exclude all others to just this selection. It's just saying that any Topic with replies less than one and also called "Conversation" should be excluded from the final return.
Any ideas?
Update
Sort of a half-hashed idea :
items_table = Arel::Table.new(scope)
unstarted_conversations = scope.select{|a| a.title == "Conversation" && a.replies.count > 0}.map(&:id)
scope.where(items_table[:id].not_in unstarted_conversations)

A:

You can use something called count cache, basically what it does is add a field to the table and store in that field the total of "associates" of the specified type and is automatically updated.
Checkout this old screen/ascii cast: http://railscasts.com/episodes/23-counter-cache-column?view=asciicast
Here is something newer: http://hiteshrawal.blogspot.com/2011/12/rails-counter-cache.html
In your case would be as follow:
# migration
class AddCounterCacheToTopìc < ActiveRecord::Migration
  def self.up
    add_column :topics, :replies_count, :integer, :default => 0
  end

  def self.down
    remove_column :topics, :replies_count
  end
end

# model
class Replay < ActiveRecord::Base
  belongs_to :topic, :counter_cache => true
end

Hope it help you.

