order by association's count of another model, limit to 1

Question

I want to display a gallery of magazines that shows the newest issue for each magazine title, but ordered by the number of subscriptions on that magazine. I came up with this query

class IssuesController < ApplicationController
    def index
      @issues = Issue
        .includes(:magazine)
        .order('magazines.subscriptions_count DESC')
        .order(:release)
        .paginate(page: @page, per_page: 25)
    end
end

It correctly orders them by the suscriptions_count, however there are many entries for each magazine, each issue is displayed in order. I just want the first magazine for each one. I've tried adding uniq to the query, group(:magazine_id), limit(1), and more but I can't get it to only display one issue per magazine title.

possible duplicate of [Rails order by results count of has\_many association](http://stackoverflow.com/questions/16996618/rails-order-by-results-count-of-has-many-association) — infused, Aug 23 '14 at 04:47
No, this already has a counter cache applied, and I am getting the correct results already, just too many for each. I think I need to group them by `magazine_id` and then somehow only display one of each, but when I add a `group('magazine_id`) I get a `PG::Error column "issues.id" must appear in the GROUP BY clause or be used in an aggregate function` — tehfailsafe, Aug 23 '14 at 16:15

score 0 · Answer 1 · edited May 23 '17 at 11:43

I found this answer PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function

which lead me to select("DISTINCT_ON('magazine_id') *"). Works perfectly, only showing a single entry for each magazine_id

def index
  @issues = Issue
    .includes(:magazine)
    .select("DISTINCT ON (magazine_id, magazines.subscriptions_count) *")
    .order('comics.subscriptions_count DESC')
    .paginate(page: @page, per_page: 25)
end

order by association's count of another model, limit to 1

1 Answers1