Rails Active Record Query - Categorization, sorting based on 'updated_at' value

Question

In my Rails web application (Rails 3.2.9, Ruby 1.9.3), there is an article dashboard page specific to each user where all articles that he/she creates gets listed. There are some main categories under which there articles should get listed. The Article Details are displayed in a table structure.

Sl#     Name           Reference       Updated At

Category 1
1       Article 1      abc abc         05/16/2016 11:12AM
2       Article 2      pqr stu         05/14/2016 10:11PM
3       Article 3      abc abc         05/13/2016 05:05AM

Category 2
4       Article 4      abc abc         05/16/2016 11:00AM
5       Article 5      pqr stu         05/15/2016 06:41PM

Category 3
6       Article 6      abc abc         05/16/2016 10:12AM
7       Article 7      pqr stu         05/01/2016 09:52PM
8       Article 8      abc abc         05/12/2016 12:05PM

So, there are 8 articles created by this user, say 'Ramu' and which are categorized under 3 categories. Articles can be updated any time. The first rule is that

The category which has the latest updated article needs to be shown on top and so on

So, here you could see Article 1 of Category 1 has been updated very latest and then Article 4 of Category 2. So, the Category 1, then Category 2 etc.

The Second rule is that Articles under a category should be sorted based on the updated_at value in DESC order.

@articles =
  @current_user.articles.order('updated_at DESC').paginate(page: params[:page], per_page: 10)

This query cannot get the results in the intended order because the Category which has the a latest updated article can have articles having lesser updated_at value compared to the articles of other category. But the latest updated article along with the other articles of Category 1 are to be listed on top and so on.

@articles =
  @current_user.articles.order('category_id, updated_at DESC').paginate(page: params[:page], per_page: 10)

The above is also not usable as it sorts based on the category_id first.

Could anyone please help me to get an optimized MySQL query for it. Thank you.

If you clarify exactly what you're looking for I'll try to help but what I'm getting from this so far is that you want the newest article to be shown first followed by all articles in that same category regardless of their timestamp? — bkunzi01
Please add your Active Record classes for Category and Article — Qaisar Nadeem

Qaisar Nadeem Qaisar Nadeem · Accepted Answer · 2016-05-16T19:44:54

class Category < ActiveRecord::Base
  has_many :articles
  #has_many :ordered_articles , -> {order(updated_at: :desc)}, class_name: 'Article', foreign_key: 'category_id'
  has_many :ordered_articles , order: "updated_at desc", class_name: 'Article', foreign_key: 'category_id'
end

class Article < ActiveRecord::Base
  belongs_to :category
end

Here is solution to your problem

@categories = Category.select("categories.id,categories.name  , max(articles.updated_at) as last_article").joins(:articles).group("categories.id").order("last_article desc").includes(:ordered_articles).where("articles.user_id = ?",current_user.id)

All of the magic is happing here.

This will return all the categories in the order you want. It will return all the categories ordered by articles updated_at column.

Secondly I added a new has_many association in category class. It's name by ordered_articles . It is just like the normal has_many :articles association with the benefit of ordered articles. It will return all the articles for a category ordered by updated_at desc . You can add these order functionality on original has_many association if you want.

Now all of the categories are available in @categories instance variable. Loop through it and get all of the articles in that category by calling ordered_articles on it. i-e

@categories.each do |category|
 puts category.name
  category.ordered_articles.each do |article|
  puts article.title
  end
end

This is what you wanted as output. You can get the other attributes as you want. I assumed your Category has a column named name and article has a column title

You can change it to your actual columns.

P.S: We can skip the new has_many association can call articles on each category object with order_by clause e:g category.articles.order(updated_at: :desc) but this will cause N+1 Queries dilemma . So to eager load the corresponding articles for each category I added a new association which returns the articles in our desired order

Rails Active Record Query - Categorization, sorting based on 'updated_at' value

1 Answers