summing child rows in HABTM table

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. :slight_smile: 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
http://www.dcmanges.com/blog

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.