How do I make named_scope work properly with a joined table?

Question

Here's my situation. I have two tables: pledges and pledge_transactions. When a user makes a pledge, he has only a row in the pledges table.

Later when it comes time to fulfill the pledge, each payment is logged in my pledge_transactions table.

I need to be able to query all open pledges which means that the sum of the amounts in the transactions table is less than the pledged amount.

Here's what I have so far:

named_scope :open,
   :group => 'pledges.id', 
   :include => :transactions, 
   :select => 'pledge_transactions.*', 
   :conditions => 'pledge_transactions.id is not null or pledge_transactions.id is null',
   :having => 'sum(pledge_transactions.amount) < pledges.amount or sum(pledge_transactions.amount) is null'

You might be asking yourself why I have that superfluous and ridiculous conditions option specified. The answer is that when I don't force ActiveRecord to acknowledge the pledge_transactions table in the conditions, it omits it completely, which means my having clause becomes meaningless.

My belief is that I have run into a shortcoming of ActiveRecord.

Ultimately I need to be able to do the following:

• Pledge.open
• Pledge.open.count
• Pledge.open.find(:all, ...)
• etc.

Anybody have a more elegant answer to this problem? Please no suggestions of incrementing a pledges amount_given field each time a transaction occurs. That feels like a band-aid approach and I'm much more of a fan of keeping the pledge static after it is created and computing the difference.

If I weren't using Rails here, I'd just create a view and be done with it.

Thanks!

Answer 1

How is the :transactions association defined? Does it stipulate :class_name = 'PledgeTransaction' (or whatever the class is, if it uses set_table_name)?

Have you looked at the :joins parameter? I think it might be what you were looking for. Certainly that :conditions thing doesn't look right.

If I weren't using Rails here, I'd just create a view and be done with it

Just because it's Rails doesn't mean you can't use a view. OK, depending on the way it's constructed you may not be able to update it, but otherwise go for it. You can create and drop views in migrations, too:

class CreateReallyUsefulView < ActiveRecord::Migration
def self.up
    # this is Oracle, I don't know if CREATE OR REPLACE is widely-supported
    sql = %{
      CREATE OR REPLACE VIEW really_usefuls AS
      SELECT
      ... blah blah SQL blah
    }
    execute sql
  end

  def self.down
    execute 'drop view really_usefuls'
  end
end

class ReallyUseful < ActiveRecord::Base
    # all the usual stuff here, considering overriding the C, U and D parts 
    # of CRUD if it's supposed to be read-only and you're paranoid
end

I think the books/docs don't go into this much because implementation of, and support for views varies significantly across platforms.

Answer 2

Hello,

I think using NOT EXISTS in your conditions will get you what you want. I'm assuming the association is on the pledge_transaction as pledge_id. Here's how I would implement #open

named_scope :open,
      :conditions =>
        "
          NOT EXISTS (
            select 1
            from pledge_transactions
            where
              pledge.id = pledge_transactions.pledge_id AND
              pledge_transactions.amount < pledge.amount
            )
        "
    }
  }

This will allow you to do Pledge.open, Pledge.open.count and Pledge.open.find_by_{what ever}.

Hope that helps

James