Ruby: Convert an array or hash to an activerecord relation

Question

I am trying to convert a hash to an activerecord relation but am not able to do so. I intend to use the activerecord relation to sort and then filter the Category table. The end goal is to create an instance method to filter the top 5 visited categories, which i can then use/call in the controller. This is what i have in mind:

Category model:

def top_5_visited
        Article.joins(:categories).group('categories.name').sum(:impressions_count)
// Add sort
// Limit to top 5 
end

Category controller:

@categories = Category.top_5 visited

A hash {"Simula"=>7, "Haskell"=>5, "JavaScript"=>10, "C#"=>112} will be created through the following query:

total_count = Article.joins(:categories).group('categories.name').sum(:impressions_count)

I have also tried to convert it to an array using sort_by method:

total_count_sorted = total_count.sort_by {|_key, value| value}

I have googled "convert array to activerecord relation" and referenced this post, but testing this:

Category.where(id: total_count_sort.map(&:id))

in the rails console, brings up this error:

NoMethodError: undefined method id for ["Simula", 7]:Array

What is the desired result? I'm guessing you want an ActiveRecord::Relation containing 5 categories but you should clarify this as the question is abigous. — max, Nov 15 '17 at 14:46
And what you want is actually to sort the records in the database based on something like a subquery. You don't need to pluck the sums out first. What RDBMS are you using? — max, Nov 15 '17 at 15:00
Hi max, correct I want an ActiveRecord::Relation containing the top 5 most visited categories. I am using Postgresql. — doyz, Nov 15 '17 at 15:11

max · Accepted Answer · 2017-11-15T15:38:33.343

What you want to do start from the inverse end (Category) and use an aggregate in the ORDER clause.

Category.joins(:articles)
        .order('SUM(articles.impressions_count) DESC')
        .group(:id)
        .limit(5)

irb(main):005:0> Category.joins(:articles).order("SUM(articles.impressions_count) DESC").group('categories.id').limit(5)
  Category Load (1.5ms)  SELECT  "categories".* FROM "categories" INNER JOIN "articles" ON "articles"."category_id" = "categories"."id" GROUP BY categories.id ORDER BY SUM(articles.impressions_count) DESC LIMIT $1  [["LIMIT", 5]]
=> #<ActiveRecord::Relation [#<Category id: 4, name: "C#", created_at: "2017-11-15 15:06:32", updated_at: "2017-11-15 15:06:32">, #<Category id: 3, name: "JavaScript", created_at: "2017-11-15 15:06:32", updated_at: "2017-11-15 15:06:32">, #<Category id: 1, name: "Simula", created_at: "2017-11-15 15:03:37", updated_at: "2017-11-15 15:03:37">, #<Category id: 2, name: "Haskell", created_at: "2017-11-15 15:06:32", updated_at: "2017-11-15 15:06:32">]>

And you should create a class method - not an instance method as this is basically just a scope and does not make sense to call on an instance.

class Category < ApplicationRecord
  has_many :articles

  def self.order_by_article_impressions
    self.joins(:articles)
        .order('SUM(articles.impressions_count)')
        .group(:id)
  end

  def self.top_5_visited
    order_by_article_impressions.limit(5)
  end

  # Or use `scope` which is just syntactic sugar
  scope(:top_5_visited) -> { order_by_article_impressions.limit(5) }
end

Thanks max, i had to put .group(:categories.id) before .order('SUM(articles.impressions_count)') if not i will get this error: ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "categories.id" must appear in the GROUP BY clause or be used in an aggregate function — doyz, Nov 15 '17 at 16:19
Thats weird - I tested it with PG and the order does not matter. `.group(:id)` also creates `GROUP BY categories.id`. If you want to be explicit you should use `group('categories.id')` with a string and not a symbol — max, Nov 15 '17 at 16:32
`.order(...).group(:id)`, `.group(:id).order(...)`, `.group('categories.id').order(...)` will give the exact same query. — max, Nov 15 '17 at 16:34
Oh, it was a typo on my part. i meant .group('categories.id'). Could it be because i have a has_many :categories, through: :article_categories and has_many :articles, through: :article_categories relationship? — doyz, Nov 15 '17 at 16:41
No, it works fine with an indirect association as well. https://github.com/maxcal/sandbox/tree/47310114 — max, Nov 16 '17 at 15:49

score 0 · Answer 2 · answered Nov 15 '17 at 17:38

0

Change the code to:

Category.where(id: total_count_sort.map(&:last))

answered Nov 15 '17 at 17:38

s1mpl3

1,456
1
10
14

Ruby: Convert an array or hash to an activerecord relation

2 Answers2