Helban Helban - 1 year ago 93
SQL Question

RoR: how to build a query containing only endorsements from people from the same company

For my statistics page I want to display the number of users and endorsements, and count and display only those that are in the same company as the currently logged in user.

My Users model:

class User < ActiveRecord::Base
has_many :inbound_endorsements, class_name: "Endorsement",
foreign_key: "endorsed_user_id",
dependent: :destroy
has_many :outbound_endorsements, class_name: "Endorsement",
foreign_key: "endorsing_user_id",
dependent: :destroy
has_many :endorsing_users, through: :inbound_endorsements, source: :endorsing_user
has_many :endorsed_users, through: :outbound_endorsements, source: :endorsed_user
belongs_to :company, :autosave => true
accepts_nested_attributes_for :company

And my Endorsements model:

class Endorsement < ActiveRecord::Base
belongs_to :endorsing_user, class_name: "User"
belongs_to :endorsed_user, class_name: "User"
validates :endorsing_user_id, presence: true
validates :endorsed_user_id, presence: true
validates :comment, presence: true, length: { maximum: 140}
scope :current, -> { where(created_at: (Time.now.beginning_of_month..Time.now)) }

Gathering the data for the statistics page in a controller:

def statistics
@users = User.where(activated: true, company_id: current_user.company)
@endorsements = Endorsement.all #wish to modify this
@data = @users.map { |user|
amount = user.inbound_endorsements.joins(:endorsed_user).group(:name).count
if !amount.empty?
{name: user.name, data: amount}


I thought of constructing something along those lines:

@endorsements = Endorsement.joins(User).where(endorsing_user.company == current_user.company)

To Select only those endorsements where the endorsing_users company_id is the same as the current_user one.

I will appreciate any help! If you need more information, please let me know.

Best regards,

Answer Source

Start from current_user's side,

Make a scope on endorsement model

# endorsement.rb
scope :endorsed_by_company, -> (company) { joins(:endorsing_user).where("users.company" => company) }

And finally, it would look something like this

@endorsements = Endorsement.endorsed_by_company(current_user.company)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download