Geek Answers Handbook

How to make named_scope work with a joined table?

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

Later, when it comes time to fulfill a promise, each payment is recorded in my pledge_transactions table.

I need to be able to request all open promises, which means that the sum of the amounts in the transaction table is less than the declared amount.

Here is what I still have:

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 may ask yourself why I have this extra and ridiculous version of the conditions. The answer is that when I do not force ActiveRecord to recognize the pledge_transactions table in conditions, it completely excludes it, which means that my article makes sense.

I am convinced that I ran into a lack of ActiveRecord.

Ultimately, I will need to do the following:

  • Pledge.open
  • Pledge.open.count
  • Pledge.open.find (: everything, ...)
  • and etc.

Does anyone have a more elegant answer to this problem? Please do not suggest increasing the number of declared amount_given values ​​each time a transaction occurs. This is similar to the group help approach, and I am much more a fan of maintaining static after creating it and calculating the difference.

If I did not use Rails here, I would just create a view and do it with it.

Thanks!

+4
source share
2 answers

How is association defined :transactions ? Does this describe :class_name = 'PledgeTransaction' (or something else that the class if it uses set_table_name )?

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

If I didn't use Rails here, I would just create a view and do it with it

Just because Rails does not mean you cannot use a view. OK, depending on how it is built, you may not be able to update it, but go otherwise. You can also create and drop views in migrations:

 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 supposed to be read-only and you're paranoid end

I think that books / documents do not delve into this because the implementation and support of representations vary significantly across platforms.

+1
source

I think that when using NOT EXISTS in your conditions you will get what you want. I assume the association is on pledge_transaction as pledge_id . Here is how I could 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 execute Pledge.open, Pledge.open.count, and Pledge.open.find_by_ {what ever}.

+1
source

More articles:


All Articles