Order Products by Association Count

Order products by association count

When retrieving the Products, you could do:

 @products = Product.find(:all, :include => :sales, :order => "sales.value DESC")

'sales.value' can be replaced with whatever value you are trying to order by...

---

EDIT: Disregard the rest of my answer. it won't return the Product objects in descending order by sale value, it'll return the Sales objects associated with the Product in descending order by sale value. :P

Also, you can specify the ordering in your model like:

    Class Product
        has_many :sales, :order => 'sale_value DESC'
    end

where the 'sale_value' is whatever you're trying to order by... and doing it this way, to retrieve the Products, you can just do:

 @products = Product.all

order products by association count belongs_to

If you want to stay with activerecord you can use Calculations but it will take 2 queries to accomplish it:

>> brands = Sale.count(:id, :include => :brand, :group=> 'sales.brand_id', :limit => 5)
  SQL (0.7ms)   SELECT count(DISTINCT `sales`.id) AS count_id, sales.brand_id AS sales_brand_id FROM `sales` LEFT OUTER JOIN `brands` ON `brands`.id = `sales`.brand_id GROUP BY sales.brand_id LIMIT 5
=> #<OrderedHash {967032312=>3, 1392881137=>1}>

>> brands_with_most_sales = Brand.find(brands.keys)
  Brand Load (0.5ms)   SELECT * FROM `brands` WHERE (`brands`.`id` = 967032312) 
=> [#<Brand id: 967032312, title: "brand-x", created_at: "2009-11-19 02:46:48", updated_at: "2009-11-19 02:46:48">]

Otherwise you might want to write you own query using find_by_SQL

Rails sort by association count and associated field on same model

So there are 2 problems here:

1. You are using .joins on a belongs_to association that may not exist.
2. You are joining the same user table twice (for 2 different associations), causing rails to generate a table alias so that the SQL will be valid. But you aren't grouping on this alias.

Solution:

You'll need a LEFT JOIN which unfortunately isn't as pretty as a standard join. You'll also need to modify your .group clause:

Department.
  select("departments.*, COUNT(users.id) AS members").
  joins(:members, "LEFT JOIN users directors ON departments.director_id = directors.id").
  group("departments.id, directors.last_name").
  order("members, directors.last_name")

Rails order by results count of has_many association

If you expect to use this query frequently, I suggest you to use built-in counter_cache

# Job Model
class Job < ActiveRecord::Base
  belongs_to :company, counter_cache: true
  # ...
end

# add a migration
add_column :company, :jobs_count, :integer, default: 0

# Company model
class Company < ActiveRecord::Base
  scope :featured, order('jobs_count DESC')
  # ...
end

and then use it like

@featured_company = Company.featured

Order by count of a model's association with a particular attribute

With joins (INNER JOIN) you'll get only those users, who have at least one appointment associated:

User.joins(:appointments)
    .where(appointments: { type: 'typeB' })
    .group('users.id')
    .order('count(appointments.id) DESC')

If you use includes (LEFT OUTER JOIN) instead, you'll get a list of all users having those without appointments of 'typeB' at the end of the list.

Go gorm order by association count

There is no such feature like rails counter cache in the gorm, but gorm has callbacks before* & after* so it is easy to implement order by collection count feature.

For example:

// Updating data in same transaction
func (c *Collection) AfterUpdate(tx *gorm.DB) (err error) {
  tx.Model(&Collection{}).Where("id = ?", c.ID).Update("items_count", gorm.Expr("items_count + ?", 1))
  return
}

Rails 3 ActiveRecord: Order by count on association

Using named scopes:

class Song
  has_many :listens
  scope :top5,
    select("songs.id, OTHER_ATTRS_YOU_NEED, count(listens.id) AS listens_count").
    joins(:listens).
    group("songs.id").
    order("listens_count DESC").
    limit(5)

Song.top5 # top 5 most listened songs

---

Related Topics