Count repeating rows only once

Ruby on Rails
1

I have a table which is storing some data, and I am using this quieris to get that data.

def self.position_by_day(start, current_user)

 user_projects = Project.where(user_id: current_user.id).first                                                                 
 results = user_projects.reports.where(created_at: start.beginning_of_day..Time.zone.now)                  
 results = results.group("date(created_at)")                                                                                   
 results = results.select("date(created_at) as created_at, count(*) as count")                                                 
 results.each_with_object({}) do |result, positions|                                                                          
   positions[result.created_at.to_date] = result.count                                                                        
 end                                                                                                                            end

The report table has a column key_id, and one position, so key_id can have multiple positions, so a count will get the total no of key_id’s but I would like to count the repeating key_id only once.

I know that ActiveRecord has distinct but I don’t really know how to use it here. And by adding distinct to the end of :

results = user_projects.reports.where(created_at: start.beginning_of_day..Time.zone.now)

will not work.

Thank you.

2

created_at: start.beginning_of_day..Time.zone.now

I didn’t know you could do that with ranges in Arel. I was always writing the sql by hand in those cases. Thanks for that.

so a count will get the total no of key_id’s but I would like to count the repeating key_id only once.

I’m having trouble following your data model, but if I understand correctly, doing this in SQL would be tough - you essentially want to join but only one the first occurring row. Doing this in Arel would probably be even harder than doing it in SQL. If you’re committed to ARel and SQL here then I’d suggest creating a SQL view in the database and basing a model off that view. Views are off the beaten path in rails though.

You can do this by using uniq from Enumerable, passing it a block.

results = results.uniq { |result| result.key_id == result.key_id }

I think that will do it, but I’d also take a long hard look at the data model and see if there’s a cleaner way to do all of this.

3

It has no effect.

4

As I mentioned, I’m finding it pretty difficult to follow your data model or even the result you’re looking for. So far, I understand you have:

users:

  • id

user_projects:

  • user_id
  • report_id
  • project_id (presumably)

reports:

  • key_id
  • position
  • created_at

Is this it? What question are you trying to answer with this schema?

5

The schema:
User
Project → ProjectKey - Key

Report:
project_id
key_id
position
created_at

So for a user, I want to get all the keys from all his projects, and display the evolutions of keys in a chart(thats why i need created_at) but because a keys have more positions, I only need to count the key once.

Thank you.

6

So if I understand correctly, a project has many keys through a report?

I’m not sure what you mean by “evolution of keys”. Are you trying to get the count of new keys added broken up by day?

7

Yes joelq, that’s what I ment by evolution, but for all the keys, new and old.

8

I have a Report table: id, project_id, key_id, position, created_at.

And the fallowing code, which is going to populate a graph, the code that I have now, is working good, but the problem that I’m facing is that a key_id has multiple positions, and I only want to count a key_id with 3 positions (for example), once. I tried using distinct but it didn’t work.

Thank you

def self.chart_data(start = 1.weeks.ago, current_user) 
  positions = where("position <=  50").position_by_day(start, current_user)   
  (start.to_date..Date.today).map do |date|
    {
      created_at: date,  
      position: positions[date] || 0,
    }
  end                    
end                      
 
def self.position_by_day(start, current_user)
  user_projects = Project.where(user_id: current_user.id).first
  results = user_projects.reports.where(created_at:   start.beginning_of_day..Time.zone.now)
  results = results.group("date(created_at)")
  results = results.select("date(created_at) as created_at, count(*) as count")
  results.each_with_object({}) do |result, possitions|
    possitions[result.created_at.to_date] = result.count
  end                   
end

Also to get all the ‘keys’ that a user can have, I need to get all the projects for a user, and then get all the keys through a loop on each project. How can I do this? Can I wrap the results in a loop? Or is it a better way?

Thank you again.