How to build select cmd on rails active model?

Question

User Table

id| name  | email          | invited_by_id  
1 | jhon  | [email protected]  | null  
2 | sarah | [email protected] | 1  
3 | baby  | [email protected]  | 2  

As User table you'll see sarah's invited by jhon and baby's invited by sarah

Expect Result

id| name  | email         | invited_by_id | invited_by_name  
1 | jhon  | [email protected] | null          | null  
2 | sarah | [email protected]| 1             | jhon  
3 | baby  | [email protected] | 2             | sarah 

How to create select cmd or best way to get the result as my expect on rails active model ?

Answer 1

user.rb

def inviter_name
  if self.invited_by_id.nil?
    nil
  else
    User.find(self.invited_by_id).name
  end
end

or you can use belongs_to

belongs_to :inviter, class_name: "User", foreign_key: 'invited_by_id'

and the inviter name can be got like below:

@user.inviter.name

Answer 2

@Zoker is correct, you should add a 'belongs_to' clause to your model definition:

belongs_to :invited_by, class_name: "User"

Once done, the request could be as simple as this:

User.all.each do |user|
  puts [user.name, user.email, user.invited_by_id.to_s, user.invited_by.name].join(", ")
end

This will print out a (very) basic CSV list of the results you require. This makes most sense in a console session - if you put this code in a controller you'd end up with it getting lost in the server output.

For large lists you will get a performance hit using this look-up. For each row, a separate request will be made to the database to find the 'invited by' user so we can read the name.

Better is to do this:

User.includes(:invited_by).each do |user|
  puts [user.name, user.email, user.invited_by_id.to_s, user.invited_by.name].join(", ")
end

This tells Rails to make one more query, which gets all the included 'invited_by' users in one go. This means that instead of making (number_of_rows + 1) queries you're now making just 2.

(Code untested at time of writing.)