I have articles that are able to be tagged in categories. I am struggling to create a query which will extract the sum of view count (tracked using impressionist gem) of articles within each category.
Schema:
create_table "article_categories", force: :cascade do |t|
t.integer "article_id"
t.integer "category_id"
end
create_table "articles", force: :cascade do |t|
t.string "title"
t.text "description"
t.bigint "user_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "impressions_count", default: 0
end
create_table "categories", force: :cascade do |t|
t.string "name"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
Article model
class Article < ApplicationRecord
is_impressionable :counter_cache => true, :unique => false
belongs_to :user
has_many :impressions, as: :impressionable
has_many :article_categories
has_many :categories, through: :article_categories
end
Category model
class Category < ApplicationRecord
has_many :article_categories
has_many :articles, through: :article_categories
end
ArticleCategory model
class ArticleCategory < ActiveRecord::Base
belongs_to :article
belongs_to :category
end
Here is what i have tried so far and the errors when i test in rails console:
cat_group = Article.joins(:article_categories).group_by(&:category_ids)
// this query results in an array of articles within each category
1) cat_group.sum("impressions_count")
//TypeError: no implicit conversion of Array into String
2) cat_group.select("sum(impressions_count) AS total_count")
//ArgumentError: wrong number of arguments (given 1, expected 0)
3) Article.joins(:article_categories).group(:category_id)
//ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function
If I understand you correctly and view count is impressions_count
, here is query that you can use:
Article.joins(:categories).group('categories.name').sum(:impressions_count)