Hi,
I'm trying to sum up the records in a related table that used to just be a child table. This line used to work:
table1.sum(:enrich_bead_count_hemo, :conditions => "table2_id = " + id.to_s)
now I've got an intermediate "has and belongs to many" = HABTM table inbetween them. Could somebody help me figure out how to sum across two tables? I'm thinking i might have to use the custom SQL "join" attribute of the sum method.
Thanks,
Dave
You should take advantage of the association proxies. Something like this should work.
class Magazine has_many :subscriptions has_many :subscribers, :through => :subscriptions end
class Subscription belongs_to :magazine belongs_to :subscriber end
class Subscriber has_many :subscriptions end
Now you can do calculations like this:
class Magazine def youngest_subscriber_birthday subscribers.calculate(:max, :date_of_birth) end end
You should be able to do the calculation the same way for your 'enrich_bead_count_hemo'.
-Dan Manges http://www.dcmanges.com/blog
Thanks for the response,
However, I tired what you suggested but only got to the same point as before.
Both of these ways:
table1.calculate(:sum, :enrich_bead_count_hemo, OR table1.sum(:enrich_bead_count_hemo
give me the same answer: at total of *all* the rows in the other table. But I want a sum of *just* the *associated* rows.
I was able to do this when the tables where directly related (with for HABTM or "has many :foo => :through"
Dave
Hi Dave,
Can you post more of your code? If you're going through the association proxies, the calculation should work. Is 'table1' in your example a target of a has_many :through association? So do you have it set up something like this?
class SomeModel < ActiveRecord::Base has_many :table1, :through => :something end
some_model = SomeModel.find(:first) some_model.table1.sum(:enrich_bead_count_hemo)
If you're just doing Table1.sum(:enrich_bead_count_hemo), (note the capitalized Table1), then it would be for all the rows, not just the associated ones.
-Dan Manges http://www.dcmanges.com/blog
Lets just use your Magazine / Subscriber example for now.
I want to do something like this:
I think I understand what you're trying to do - you don't need to do a find. Implement the method like I suggested.
has_many :subscribers, :through => :subscriptions
def some_method subscribers.calculate(:max, :date_of_birth) end
"subscribers" references the has_many :through association proxy. It will take care of setting the proper joins and conditions to run the calculation only for the associated records. In my example with calculate(:max, :date_of_birth), the method would find the date of birth for the youngest person subscribed to that magazine. Make sure you're using lowercase "subscribers", not capitalized "Subscriber"
-Dan Manges http://www.dcmanges.com/blog
You need to implement subscriber_salaries like this:
class Magazine has_many :subscriptions has_many :subscribers, :through => :subscriptions def subscriber_salaries subscribers.calculate(:sum, :salary) end end
-Dan
Thanks Dan that worked!
I suppose at some point here I should learn ruby. 
The language
(and rails platform) give you so much for free and the syntax is so
intuitive that Its easy to get way in over your head fast when the
subtle but inevitable complexities start to counteract the initial
advantages! (perhaps)
At the very least I should figure out the difference between these three object naming conventions
Subscriber # CamelNotation (the class?) subscriber # lowercase_underscore (an instantiation of the class? ie an object?) :subscriber # :colon_lowercase_underscore (an accessor? what ever that is.)
(not to mention the plural vs singular conventions)
Its very confusing for newbies
Thanks though, this one trick should keep me from stumbling over my own ignorance for a while.
Dave
In the Rails world, your class names tend to be CamelCased. Instances of that class can be assigned toVariables of_any_look. Standard Ruby convention is to keep capital letters out of variables, except for Constants, which are always capitalized.
In RoR, when you're doing a datamigration, the data_migrations table will always link to your DataMigration model.
:symbols are something else entirely - they're an object with a name. You can think of them as something like the interpreted language's answer to the enumerated integer - they're objects with O(1) comparison, constant size, and once you declare them and pay for the memory leak of creating them you never have to re-allocate memory for them, like you would for strings.
However, they're not strings! If you care about the content of the symbol, don't use a symbol. If you just care about the symbol being a reference for something, it's a great choice - which is why you see them all over the place in the RoR world for option hashes: We don't care that :if means 'if' which is a conditional in the English language, we just need a constant, agreed-upon, and memorable way to refer to something passed to the method.
Symbols are also closely tied to the way that methods are declared, which is why you see things like:
before_filter :do_this.
... because it's much faster to send :do_this to the controller for evaluation than it is to eval 'do_this' and get the parser involved.
Hey Dan,
Got another question about this.
What if salary is not really a column in the database?
What if it's a variable which is just a function of two other database columns?
For example what if 'salary' is a variable defined in the Magazine object but it's either 'gross_income' or 'gross_pay' (depending on which is selected via a third column's value) ?
When I try to use the non-column *variable* salary below, i get the following error:
"PGError: ERROR: column "salary" does not exist"
Basically I'm just asking how to sum up related objects' variables. (which are not stored in the database)
dschruth wrote:
What if salary is not really a column in the database?
What if it's a variable which is just a function of two other database columns?
Subscriber.calculate( :sum, :salary, :select => 'compensation + perks AS salary', :conditions => { :magazine_id => id } )
Phlip wrote:
dschruth wrote:
What if salary is not really a column in the database?
What if it's a variable which is just a function of two other database columns?
Subscriber.calculate( :sum, :salary, :select => 'compensation + perks AS salary', :conditions => { :magazine_id => id } )
Occurs to me the :sum and :select might conflict.
Try also:
Subscriber.find( :first, :select => 'sum(compensation + perks) AS salaries', :conditions => { :magazine_id => id } )
You could also use :all, then group by 'magazine_id', to just rip them all at once.
Hi Dave,
You can use methods from the Enumerable module to get a list of salaries and calculate the sum. Try something like this:
def subscriber_salaries subscribers.map(&:salary).sum end
That will loop over each subscriber, build an array of the salaries, and then calculate the sum. I would use this approach unless due to the size of the data set you want to bring back the result directly from the database.
Hope this helps,
Dan Manges wrote:
def subscriber_salaries subscribers.map(&:salary).sum end
That's inefficient. If you measure your application and find it will cause a bottleneck, you must put the sum, and the addition, on the database side. That crunches the numbers while they are much closer to the hard drive.
(However, if it's not time-critical code, stay with that solution, because it's easier to type in Ruby!!)
Thanks dan, I'll use this one for now.. We're still a pretty small, internal, few-records shop.