How to get record which have min has_many rec ords(joins data)

Question

user.rb

has_many :properties

property.rb

belongs_to :user

I want to get a user who have min properties like wise for max also.

I cant find any query related to that

Answer 1

To find the user with min properties you can simply do,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").last

And to find the user with max properties,

User.joins(:properties).group("properties.user_id").order("count(properties.user_id) desc").first

Note: Because its a join operation with properties, so user with no properties will not appear in this query.

Answer 2

You could use counter_cache.

The :counter_cache option can be used to make finding the number of belonging objects more efficient.

From here

belongs_to :user, counter_cache: true

Then create the migration:

def self.up
  add_column :users, :properties_count, :integer, :default => 0

  User.reset_column_information
  User.find(:all).each do |u|
    User.update_counters u.id, :properties_count => u.properties.length
  end
end

Then you can fetch user which have max properties_count

User.maximum("properties_count")

Here is an awesome RailsCast about counter_cache

Answer 3

I think you can do like this by scopes

class User
  has_many :properties
  scope :max_properties,
    select("users.id, count(properties.id) AS properties_count").
    joins(:properties).
    group("properties.id").
    order("properties_count DESC").
    limit(1)

  scope :min_properties,
    select("users.id, count(properties.id) AS properties_count").
    joins(:properties).
    group("properties.id").
    order("properties_count ASC").
    limit(1)

And just call User.max_properties and User.min_properties

UPDATED:

It will aslo work like BoraMa suggeted

class User
  has_many :properties
  scope :max_properties,
    select("users.*, count(properties.id) AS properties_count").
    joins(:properties).
    group("users.id").
    order("properties_count DESC").
    limit(1)

  scope :min_properties,
    select("users.*, count(properties.id) AS properties_count").
    joins(:properties).
    group("users.id").
    order("properties_count ASC").
    limit(1)