Locating unique column values and use the output for the next query

Rails 6.0.0

ruby-2.6.2

Hello I have a model called “Group” which connects to an SQLite database. This database has two columns I’m interested in, they are hits, as an integer and iteration as an integer also.

What I would like to achieve is to read the iteration column, return each unique entry (1,2,3…..50 for example) then for every entry returned perform a lookup on the hits table and sum them together.

I’ve gotten as far as finding the unique values in the iteration column with this code:

2.6.2 :001 > Group.pluck(:iteration).uniq
   (0.5ms)  SELECT sqlite_version(*)
   (0.2ms)  SELECT "groups"."iteration" FROM "groups"
 => [1, 2]

​x
 
2.6.2 :001 > Group.pluck(:iteration).uniq   (0.5ms)  SELECT sqlite_version(*)   (0.2ms)  SELECT "groups"."iteration" FROM "groups" => [1, 2]​

This is expected as I have only two unique values so far.

And also able to sum together the hits column whilst filtered on iteration.

2.6.2 :001 > Group.where("iteration = 1").where("hits >0").sum(:hits)
  (0.5ms)  SELECT sqlite_version(*)
  (0.1ms)  SELECT SUM("groups"."hits") FROM "groups" WHERE (iteration = 1) AND (hits >0)
  => 143

 
2.6.2 :001 > Group.where("iteration = 1").where("hits >0").sum(:hits)  (0.5ms)  SELECT sqlite_version(*)  (0.1ms)  SELECT SUM("groups"."hits") FROM "groups" WHERE (iteration = 1) AND (hits >0)  => 143​

I think I need some sort of loop but I’m not sure how to get the value from the .pluck command into the .where command. I tried this but it didn’t work. Any suggestions on how to generate this query would be appreciated.

Group.pluck(:iteration).uniq.each do |a|
         hits_sum = Group.where("iteration = a ").where("hits >0").sum(:hits)  

 end

 
Group.pluck(:iteration).uniq.each do |a|         hits_sum = Group.where("iteration = a ").where("hits >0").sum(:hits)  ​ end​

Answer

you need to group_by iteration and sum the hits values:

Group.group(:iteration).sum(:hits)

Advertisement

Answer