CODEWITHSUNDEEP
rubyactiverecordruby-on-rails-4
John
John

Reputation: 634

Improve a activerecord query

How can I improve this query?

 f, s, t, ft, et, tf = [], [], [], [], [], []
 reports = pros.each do |pro|
  pro_report = pro.project_reports.where(project_id: project_id).size
  f << pro_report.where(position: 1).size
  s << pro_report.where(position: 2).size
  t << pro_report.where(position: 3).size
  ft << pro_report.where('position BETWEEN ? AND ?', 4, 10).size
  et << pro_report.where('position BETWEEN ? AND ?', 11, 20).size
  tf << pro_report.where('position BETWEEN ? AND ?', 21, 50).size
 end
{f: f.sum, s: s.sum, t: t.sum, ft: ft.sum, et: et.sum, tf: tf.sum}

Is it a good idea to create a Postgres VIEW? How can I do that? Any improvement on speed or structure is welcomed.

Thank you.

Upvotes: 0

Views: 49

Answers (2)

Rafa Paez
Rafa Paez

Reputation: 4860

There is no need for the loop. Do it as below

pro_ids = pros.map(&:id)
pro_reports = ProjectReport.where(project_id: pro_ids)
f = pro_reports.where(position: 1).size
s = pro_reports.where(position: 2).size
t = pro_reports.where(position: 3).size
ft = pro_reports.where('position BETWEEN ? AND ?', 4, 10).size
et = pro_reports.where('position BETWEEN ? AND ?', 11, 20).size
tf = pro_reports.where('position BETWEEN ? AND ?', 21, 50).size

Remember to have a proper index in the position column - in that case is a must.

Alternatively you can do a single query to the database grouping by position and then use Ruby Hash#select and Rails Enumerable#sum to get the desired values:

pro_ids = pros.map(&:id)
hash_pos_count = ProjectReport.where(project_id: pro_ids).group(:position).size

f = hash_pos_count.select{ |k, v| k == 1 }.first.last
...
ft = hash_pos_count.select{ |k, v| k >= 3 && k <= 10 }.sum(&:last) 
...

Upvotes: 1

Ashish Chopra
Ashish Chopra

Reputation: 1439

Since in your scenario most of the queries are on position field I would suggest making and index on that field.

Upvotes: 1

Related Questions