CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4
vanilla_skies
vanilla_skies

Reputation: 415

Ordering based on another model attribute - Ruby

I have two models. Category and Photo. There are many photos for each category.

I want to order a category object array in such a way that it is ordered by an attribute in the Photo model. Specifically how many times a photo is downloaded.

I am trying to initiate the instance variable with something like

@category = Category.active.order('photos.qty_download DESC)

I would then use that instance variable to list the categories and how many photos have been downloaded for each category

Chicago   45
San Fran   32
Boston    23
etc

What expression will work in Rails?

Thanks

Upvotes: 0

Views: 88

Answers (2)

Suneel
Suneel

Reputation: 381

Category.includes(:photos).order(qty_download: :desc).reference(:photos)

Upvotes: 0

Todd Agulnick
Todd Agulnick

Reputation: 1985

Let's assume that your models look something like this:

class Category < ActiveRecord::Base
  has_many :photos
end

class Photo < ActiveRecord::Base
  belongs_to :category
end

Let's also assume:

  • each photo has a qty_download field as you describe in your question
  • each category has a name field
  • the goal is to order the display of categories according to the sum of all the qty_download for all of the photos in each category
  • it's okay to exclude entirely categories that have no associated photos

With those assumptions, you should be able to write

Category.
  joins(:photos).
  group("categories.id, categories.name").
  select("categories.id, categories.name, sum(photos.qty_download) as total_downloads").
  order("total_downloads desc")

Note that what will be returned will be "partial" category objects that contain only id, name, and total_downloads. If you want additional category fields, add them to both the group and the select clauses.

Update

Newer versions of Postgres allow a simplification of the query:

Category.
  joins(:photos).
  group("categories.id").
  select("categories.*, sum(photos.qty_download) as total_downloads").
  order("total_downloads desc")

Note that in recent versions of Postgres, you can simply group by a unique id -- you no longer must group by all attributes that you which to include in your select. So you can now include all the attributes of categories as categories.*. I'll leave the original in place as it is (marginally) more efficient and backwards-compatible with older versions of Postgres.

Upvotes: 2

Related Questions