Math between different records

Hi all.

Could you please recommend elegant way (read - best practices in
“Rails way”, less DB calls) to solve the following situation ?

I have 2 tables:

Meter

  • has_many :meter_records

MeterRecord

  • belong_to :meter

I have to calculate difference between meter’s current month record
and same meter’s previous month record.

I would like to avoid unnecessary selects from DB and also avoid
dealing with SQL - I prefer functionality provided by ActiveRecord.

Can you please recommend the solution or at least point me to a good
tutorial ?

Thank you in advance.

Artūras

I have to calculate difference between meter’s current month record
and same meter’s previous month record.

I would like to avoid unnecessary selects from DB and also avoid
dealing with SQL - I prefer functionality provided by ActiveRecord.

Can you please recommend the solution or at least point me to a good
tutorial ?

Thank you in advance.

No idea if this is best practice, but I’d put it in a method in the
Meter model and just do it…

class Meter…

def meth
c, l = meter_records.order(‘created_at desc’).limit(2)
c.some_field - l.some_field
end

end

I’m making some assumptions about your models of course, but the above
should get you started.

-philip

“ArtūrasB.” [email protected] wrote in post #991441:

Could you please recommend elegant way (read - best practices in
“Rails way”, less DB calls) to solve the following situation ?

I have 2 tables:

Meter

  • has_many :meter_records

MeterRecord

  • belong_to :meter

I have to calculate difference between meter’s current month record
and same meter’s previous month record.

I would like to avoid unnecessary selects from DB and also avoid
dealing with SQL - I prefer functionality provided by ActiveRecord.

Having some experience with this sort of thing, I would consider adding
a summary table to your database. It may seem counter-intuitive since
we’re aways told to avoid data duplication, but in the case of reporting
I often make this exception. There can be incredible performance
benefits to be gained by flattening, and pre-calculating data used in
reports.

I would create a simple flat table that records monthly summations for
each customer. This makes reporting fast and efficient. I would keep the
normalized data as well for showing detailed transaction reports. You’ll
need good automated tests to ensure your model logic always keeps the
data in sync, and possibly a batch process for regenerating the summary
data in case it ever does get out of sync.

This may be overkill for your application. It really depends on how many
customer records you expect to have. The implementation is pretty
straight forward. Every time you add a new meter reading, you also
update the summary. The safest way to do that is to use the AR aggregate
features to sum the details records and replace the summary value. As
opposed to adjusting the sum by adding the value to the previous sum.
This should be fast enough to be unnoticeable to the user when performed
on an individual customer’s records. Also, don’t forget to adjust the
summation for updates and deletes along with inserts.

This could even be implemented as database triggers if you want to
ensure that this happens no matter whether it’s your application doing
the updating or some external application performing the changes.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs