CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby-on-rails-3
fujisan
fujisan

Reputation: 221

Best way to Query Records

I'm having trouble with the performance of my rails application. This might be the effect of a poor database structure. And the page is frequently viewed by users, considering that the record is 5k+.

Current System:

Model: 
person has_many payment

Controller:
@total_payments = 0
person.each do |p|
  @total_payments += p.payments.map(&:value).sum
end

View:
@total_payments

Upvotes: 0

Views: 60

Answers (3)

tihom
tihom

Reputation: 8003

Assuming persons is an Activerecord relation

persons.joins(:payments).sum(:value)

If persons is an array already (less preferred)

Payment.where(:person_id => persons.map(&:id)).sum(:value)

If total payment is the only attribute need from associated payments in this view. Then the fastest method would be to make total_payments a field in person table. Update it whenever an associated payment is made. Something similar to counter cache. Then you won't need to make a sql query for payments at all.

Upvotes: 2

kobaltz
kobaltz

Reputation: 7070

Could you try

@total_payments += p.payments.pluck(:value).sum

This will only select the value from each payment instead of all fields in the table. Are your tables indexed on the person_id in the Payment table? This would also help speed up the database.

(on mobile atm so cannot dive much deeper)

Upvotes: 0

Jordan Denison
Jordan Denison

Reputation: 2737

You could try doing something like Payment.select('value') as a base query so that you are only doing one query and mapping over that instead.

Upvotes: 0

Related Questions