CODEWITHSUNDEEP
mysqlruby-on-railsruby
Killerpixler
Killerpixler

Reputation: 4050

Ruby loops to add things together

Let's say I have the following table data

id count
1  5
1  6
2  7

and I want to have an array at the end with {[1,11][2,7]}. How do I do this most efficiently?

So far I have something like:

IDs = Array.new
table.each do |t|
    if !IDs.include? t.id
       IDs << t.id
    end
end

and then i was thinking of going along the lines of IDs.each do |i| and have it add to a temporary variable... and then eventually returning an 2d array.

Is there a simpler way? This all comes from a mysql database so how could i run something like SELECT id, sum(count) FROM table GROUP BY id in a ruby on rails app?

EDIT:

Here is the migration that created the articles table

 class CreateArticles < ActiveRecord::Migration
 def change
   create_table :articles do |t|
    t.integer "nid"
    t.string "title"
    t.string "section"
    t.integer "views"
    t.date "date"
    t.integer "user_id"
  end
 end
end

The nid integer is the one I want to aggregate it based upon (i.e. the query would be SELECT nid, title, section, sum(views), date FROM users WHERE user_id=? I then need a way to pass in the model the articles belongs to (1-many) that is the user table.

My article.rb file only has belongs_to :user in it and user.rb has has_many :articles

EDIT 2:

I made a named scope in my article.rb file called :sum with lambda {find_by_sql("Select .... ")}

the question is now how do I put the wildcard in there for the WHERE user_id = ? so that I only grab the articles for the right user? right now my user_controller has the @articles = Articles.sum parameter in it.

Upvotes: 0

Views: 80

Answers (2)

BroiSatse
BroiSatse

Reputation: 44695

You can always execute any sql query using:

ActiveRecord::Base.connection.execute('SELECT id, sum(count) FROM table GROUP BY id').values

However this is not ideal, as it is not db agnostic. Usually queries like that are being handled very well by models, however query structure depends on how your models are defined, so no more help until you share those with us.

UPDATE:

After some discussion it seems like the best way to go is redesigning your models. Instead of having multiple duplicates of an article with different view_counts, create DailyViews model conaining id, article_id and views_count. Associate it with Article model via has_many association. Then you can simply call:

article.daily_views.sum(:views_count)

to count all the views on given article. I would probably wrap the above into another method total_views. You would also be able to define scopes on a new model, so it will make your live much easier to calculate view count for given month or year.

Upvotes: 1

Anil Maurya
Anil Maurya

Reputation: 2328

a = Article.select('nid, sum(views) as count').group('nid')

 a.inject({})do |res, i |
  res[i.status] = i.total
   res
  end

it will give you result like { '1' => '11', '2' => '7'}

Upvotes: 0

Related Questions