How to .limit order by DESC, then order by (:id)?

Question

This limits the tags to the top 20 with the highest tag count:

tag.rb

scope :top_20, -> {
  where("taggings_count != 0").order("taggings_count DESC").limit(3)
}

I want to then order those 20 according to their :id instead of by their tagging_count

application_controller

before_action :tag_cloud
def tag_cloud
  @tags = Tag.all.top_20.order(:id)
end

.order(:id) unfortunately doesn't work. They are still showing in descending order by count.

_tags.html.erb

<% tag_cloud(@tags, %w(css1 css2 css3 css4)) do |tag, css_class| %>
  <%= link_to tag.name, tag_path(tag), :class => css_class %>
<% end %>

Please let me know if you need further code or explanation to help you help me :-]

Answer 1

The query will only take the first order into consideration. You can override it with reorder however this won't work in your case since it will override the order("taggings_count DESC").

What works for you is sort:

@tags = Tag.all.top_20.sort{ |x,y| x.id <=> y.id }

Answer 2

Not very clear about your question, do you want to order record as desc or asc order?

Write an scopes as following

scope :order_id_asc, -> { order('id ASC') }
scope :order_id_desc, -> { order('id DESC') }

And re-write your code as following, with calling any one of scopes

def tag_cloud
    @tags = Tag.top_20.order_id_asc
end

Answer 3

If you want to do this with pure SQL instead of using Ruby sort, you have to do it with a subquery:

Tag.from(Tag.top_20, :tags).order(:id)

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from

Answer 4

I would rather rewrite this scope like so:

from(
  where.not(taggings_count: 0)
     .order(taggings_count: :desc)
     .limit(20)
        .as(table_name)
)

What this does is modify a FROM-clause to fetch results not from 'tags' table, but from a temporary result set (fetched in a subquery) aliased to have the same name (table_name returns just that) to fool ActiveRecord into thinking we're querying the same source. You can apply any operations on top as if it was a whole table.

As for the as-method, that doesn't seem to be mentioned anywhere... it comes from Arel, the SQL AST manager used in ActiveRecord under the hood. In fact, it's delegated to the query's Arel::SelectManager, available on arel method on any ActiveRecord::Relation.