summing child rows in HABTM table

[dschruth]

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

[Dan Manges]

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

[dschruth]

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

On Sep 20, 9:53 pm, Dan Manges <daniel.man...@gmail.com> wrote:
> 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 Mangeshttp://www.dcmanges.com/blog

[Dan Manges]

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

[dschruth]

Lets just use your Magazine / Subscriber example for now.

I want to do something like this:
--------------------------------------------------------
class Magazine
def subscriber_salaries
Subscriber.calculate(:sum, :salary, :conditions => "magazine_id =
" + id.to_s) # PGError: ERROR: column "magazine_id" does not
exist
end
end
--------------------------------------------------------
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 21, 12:40 pm, Dan Manges <daniel.man...@gmail.com> wrote:
> 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 Mangeshttp://www.dcmanges.com/blog

[Dan Manges]

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

On Sep 22, 1:41 pm, dschruth <dschr...@gmail.com> wrote:
> Lets just use yourMagazine/Subscriberexample for now.

>
> I want to do something like this:
> --------------------------------------------------------
> classMagazine
> def subscriber_salaries
> Subscriber.calculate(:sum, :salary, :conditions => "magazine_id =
> " + id.to_s) # PGError: ERROR: column "magazine_id" does not
> exist
> end
> end
> --------------------------------------------------------
> But of course there is no "magazine_id" in the subscribers table

> anymore because we've switched away from "subscriberhas 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 21, 12:40 pm, DanManges<daniel.man...@gmail.com> wrote:
>
> > 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.
>

> > -DanMangeshttp://www.dcmanges.com/blog

>
> > On Sep 21, 2:05 pm, dschruth <dschr...@gmail.com> wrote:
>
> > > 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
>

> > > On Sep 20, 9:53 pm, DanManges<daniel.man...@gmail.com> wrote:
>
> > > > You should take advantage of the association proxies. Something like
> > > > this should work.
>
> > > > classMagazine
> > > > has_many :subscriptions
> > > > has_many :subscribers, :through => :subscriptions
> > > > end
>
> > > > class Subscription
> > > > belongs_to :magazine
> > > > belongs_to :subscriber
> > > > end
>
> > > > classSubscriber
> > > > has_many :subscriptions
> > > > end
>
> > > > Now you can do calculations like this:
>
> > > > classMagazine
> > > > 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'.
>

> > > > -DanMangeshttp://www.dcmanges.com/blog

[Dan Manges]

On Sep 22, 1:41 pm, dschruth <dschr...@gmail.com> wrote:

> Lets just use your Magazine / Subscriber example for now.
>
> I want to do something like this:
> --------------------------------------------------------
> class Magazine
> def subscriber_salaries
> Subscriber.calculate(:sum, :salary, :conditions => "magazine_id =
> " + id.to_s) # PGError: ERROR: column "magazine_id" does not
> exist
> end
> 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

[dschruth]

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

On Sep 22, 1:12 pm, Dan Manges <daniel.man...@gmail.com> wrote:
> 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 Mangeshttp://www.dcmanges.com/blog

[Alex]

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.

[dschruth]

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)

[Phlip]

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

[Phlip]

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 Manges]

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

[Phlip]

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!!)

[dschruth]

Thanks dan, I'll use this one for now.. We're still a pretty small,
internal, few-records shop.

On Sep 28, 4:42 pm, Dan Manges <daniel.man...@gmail.com> wrote:
> 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,
>

> Danhttp://www.dcmanges.com/blog