Virtual Column to count record

Question

First, sorry for my English, I am totally new in ruby on rails even in very basic thing, so I hope you all can help me.

I have table Role and RoleUser table Role have has_many relationship to RoleUser with role_id as foreign key in table RoleUser is contain user_id, so I can call it 1 role have many users

and I want is to show all record in Role with additional field in every record called total_users,

total_users is in every record have role_id and count the user_id for every role, and put it in total_users,

I know this is must use the join table, but in rails I absolutely knew nothing about that, can you all give me a simple example how to do that.

and one more, same with case above, can I do for example Role.all and then the total_users in include in that without added it in database? is that use virtual column? anyone have a good source of link to learn of that

I have following code in model

def with_filtering(params, holding_company_id)
  order = []
  if params[:sort].present?
    JSON.parse(params[:sort]).each do |data|
      order << "#{data['property']} #{data['direction']}"
    end
  end
  order = 'id ASC' if order.blank?

  if self.column_names.include? "holding_company_id"
    string_conditions = ["holding_company_id = :holding_company_id"]
    placeholder_conditions = { holding_company_id: holding_company_id.id }
  else
    string_conditions = []
    placeholder_conditions = {}
  end

  if params[:filter].present?
    JSON.parse(params[:filter]).each do |filter|
      if filter['operation'] == 'between'
        string_conditions << "#{filter['property']} >= :start_#{filter['property']} AND #{filter['property']} <= :end_#{filter['property']}"
        placeholder_conditions["start_#{filter['property']}".to_sym] = filter['value1']
        placeholder_conditions["end_#{filter['property']}".to_sym] = filter['value2']
      elsif filter['operation'] == 'like'
        string_conditions << "#{filter['property']} ilike :#{filter['property']}"
        placeholder_conditions["#{filter['property']}".to_sym] = "%#{filter['value1']}%"
      else
        string_conditions << "#{filter['property']} = :#{filter['property']}"
        placeholder_conditions["#{filter['property']}".to_sym] = filter['value1']
      end
    end
  end

  conditions = [string_conditions.join(' AND '), placeholder_conditions]

  total_count = where(conditions).count

  if params[:limit].blank? && params[:offset].blank?
    data = where(conditions).order(order)
  else
    data = where(conditions).limit(params[:limit].to_i).offset(params[:offset].to_i).order(order)
  end

  return data, total_count.to_s
end

And I have follwing code in controllers

def crud_index(model)
  data, total = Role.with_filtering(params, current_holding_company)
  respond_to do |format|
    format.json { render json: { data: data, total_count: total }.to_json, status: 200 }
  end
end

My only purpose is to add virtual field called total_users, but i want added it in model and combine it with data in method with_filtering

Answer 1

If you have the models like this:

Class Role < ActiveRecord::Base
  has_many :role_users
end

Class RoleUser < ActiveRecord::Base
  belong_to :role
end

You could use select and joins to generate summary columns, but all the Role's attributes should be include in group.

roles = Role.select("roles.*, count(role_users.id) as total_users")
            .joins(:role_users)
            .group("roles.id")

Type those scripts in Rails console, Rails will generate a sql like :

SELECT roles.id, count(role_users.id) as total_users
FROM roles
INNER JOIN role_users
ON  roles.id = role_users.role_id
GROUP BY roles.id

Then you can use roles.to_json to see the result. The summary column total_users can be accessed in every member of roles.

And there are many other way can match your requirement. Such as this. There is a reference of counter cache.

My suggestion is after searching, you can test those method by rails console, it's a useful tool.

UPDATE

According to OP's update and comment, seems you have more works to do.

STEP1: move with_filtering class method to controller

with_filtering handle a lot of parameter things to get conditions, it should be handled in controller instead of model. So we can transfer with_filtering into conditions and orders in controller.

class RolesController < ApplicationController

  def conditions(params, holding_company_id)
    if self.column_names.include? "holding_company_id"
      string_conditions = ["holding_company_id = :holding_company_id"]
      placeholder_conditions = { holding_company_id: holding_company_id.id }
    else
      string_conditions = []
      placeholder_conditions = {}
    end

    if params[:filter].present?
      JSON.parse(params[:filter]).each do |filter|
        if filter['operation'] == 'between'
          string_conditions << "#{filter['property']} >= :start_#{filter['property']} AND #{filter['property']} <= :end_#{filter['property']}"
          placeholder_conditions["start_#{filter['property']}".to_sym] = filter['value1']
          placeholder_conditions["end_#{filter['property']}".to_sym] = filter['value2']
        elsif filter['operation'] == 'like'
          string_conditions << "#{filter['property']} ilike :#{filter['property']}"
          placeholder_conditions["#{filter['property']}".to_sym] = "%#{filter['value1']}%"
        else
          string_conditions << "#{filter['property']} = :#{filter['property']}"
          placeholder_conditions["#{filter['property']}".to_sym] = filter['value1']
        end
      end
    end

    return [string_conditions.join(' AND '), placeholder_conditions]
  end

  def orders(params)
    ord = []
    if params[:sort].present?
      JSON.parse(params[:sort]).each do |data|
        ord << "#{data['property']} #{data['direction']}"
      end
    end
    ord = 'id ASC' if ord.blank?    
    return ord
  end
end

STEP2: update action crud_index with conditions and orders to get total_count of Roles.

class AnswersController < ApplicationController
  def crud_index(model)
    total = Role.where(conditions(params, current_holding_company)).count

    if params[:limit].blank? && params[:offset].blank?
      data = Role.where(conditions(params, current_holding_company)).order(orders(params))
    else
      data = Role.where(conditions(params, current_holding_company)).limit(params[:limit].to_i).offset(params[:offset].to_i).order(orders(params))
    end
    respond_to do |format|
      format.json { render json: { data: data, total_count: total }.to_json, status: 200 }
    end
  end
end

STEP3: update action crud_index to get total_users by every role.

Make sure the two previous steps is pass the test.

class AnswersController < ApplicationController
  def crud_index(model)
    total = Role.where(conditions(params, current_holding_company)).count

    if params[:limit].blank? && params[:offset].blank?
      data = 
        Role.select(Role.column_names.map{|x| "Roles.#{x}"}.join(",") + " ,count(role_users.id) as total_users")
            .joins(:role_users)
            .group(Role.column_names.map{|x| "Roles.#{x}"}.join(","))
            .where(conditions(params, current_holding_company))
            .order(orders(params))
    else
      data =
        Role.select(Role.column_names.map{|x| "Roles.#{x}"}.join(",") + " ,count(role_users.id) as total_users")
            .joins(:role_users)
            .group(Role.column_names.map{|x| "Roles.#{x}"}.join(","))
            .where(conditions(params, current_holding_company))
            .order(orders(params))
            .limit(params[:limit].to_i)
            .offset(params[:offset].to_i).order(orders(params))
    end
    respond_to do |format|
      format.json { render json: { data: data, total_count: total }.to_json, status: 200 }
    end
  end
end

NOTE: step3 may need you to modify conditions and orders method to generate column_name with table_name prefix to avoid column name ambiguous error

If you can make these steps through, I suggest you can try will_paginate to simplify the part of your code about total_count ,limit and offset.

Answer 2

With what you explained, you could do something like this:

class Role < ActiveRecord::Base
  has_many :role_users
  has_many :users

  def total_users
    self.users.count
  end
end

So you just need to call the total_users method on roles object which should get you what you desire. Something like this:

Role.first.total_users 
# this will give you the total users for the first role found in your database. 

Hope it helps

Answer 3

You might want to watch this Railscast too:

#app/models/role.rb
Class Role < ActiveRecord::Base
   has_many :role_users
   has_many :users, -> { select "users.*", "role_users.*", "count(role_users.user_id) as total_users" }, through: :role_users
end

This will allow you to call:

@roles = Role.find params[:id]
@roles.users.each do |role|
   role.total_users
end

You can see more about how this works with a question I wrote some time ago - Using Delegate With has_many In Rails?

--

It's where I learnt about Alias columns, which Ryan Bates uses to count certain values: