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 M.
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
a :condition statement). But I don’t quite know how to do that yet
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 M.
http://www.dcmanges.com/blog
Lets just use your Magazine / Subscriber example for now.
But of course there is no “magazine_id” in the subscribers table
anymore because we’ve switched away from “subscriber has many
subscriptions”.
Like you said, I’m guessing I should probably use a “find” like so:
some_model = Subscriber.find( ??? )
some_model. Subscriber.calculate(:sum, :salary)
But i don’t know how to do that quite yet. (what goes in the ???)
Ideas?
On Sep 22, 1:41 pm, dschruth [email protected] wrote:
end
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
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 M.
http://www.dcmanges.com/blog
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
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.
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.
Dan M. 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.
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,
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
Sponsor our Newsletter | Privacy Policy | Terms of Service | Remote Ruby Jobs