rails query for attributes from different tables/models

Question

I am aware that similar questions have been posted in the past and I have tried to find a solution in the rails guides or in answers to previous posts, but either the answers did not work for me, or they were not what I was looking for. I have previous SQL experience (mainly MySQL) and I am currently exploring Rails for a new project.

I have a table "users" and a table "companies". A user always belongs to only one company, but a company can have many users. a simple representation of my data is:

users has the following attributes: id (INT), name(string), email(string), company_id companies has the following attributes: id (INT), company_name(string), address(string)

When I execute the following SQL query directly in the database (not in rails):

SELECT users.name, company.address FROM companies INNER JOIN users on users.company_id=companies.id

I get a 2 column reply with the users.name in the first and the company.adres to which this user belongs in the second column. As it is an inner join, companies without users or users without companies are not listed, wich is exactly what I need.

Now trying to "translate" this query into rails (I am using Rails 4.1.1), I first defined the User model and the Company model, thus creating their respective user.rb and company.rb in the models directory. For the user.rb I declared "belongs_to :company" and for the company.rb I declared "has_many :users".

After migrating etc... I use the rails console to add some data and test. All queries on single models work fine, also queries like

Users.find(1).company.address

work fine, but when I type in the following command:

e = Company.select("companies.company_name, users.name").joins(:users)

it shows that the following SQL query is generated by rails:

SELECT companies.company_name, users.name FROM "companies" INNER JOIN "users" ON "users"."company_id" = "companies"."id"

which I suppose is the same query as the one I use when I access the DB directly. After showing the query, it shows the result, but the users.name attribute is not given. The result is an array with the correct number of elements (i.e. no users without company or no companies without users are shown), but only the attribute for the Company model is given, the user.name attribute is missing:

#<ActiveRecord::Relation [#<Company id: nil, address: "addresexample1">, #<Company id: nil, address: "addresexample3">, #<Company id: nil, address: "addressexample4">]>

Am I doing something wrong, or do I expect something that is not correct in Rails ?

Try giving it like this e = Company.joins(:users).select("companies.company_name, users.name") — Pavan
– Pavan, Commented Jun 12, 2014 at 12:04
Try companies = Company.select("companies.company_name AS company_name, users.name AS user_name").joins(:users) and for displaying: companies.each{ |c| puts c.company_name, c.user_name } — cristian
– cristian, Commented Jun 12, 2014 at 12:16
maybe even e = Company.joins(:users).select("companies.company_name, users.name") works based on guides.rubyonrails.org/… — jyrkim
– jyrkim, Commented Jun 12, 2014 at 12:19
@pavan, thanks for the suggestion. As you may see below, the problem was not how I formulated the query but rather how I treated its results. — user3731811
– user3731811, Commented Jun 12, 2014 at 14:02
@Octopus-Paul: Thanks for the suggestion, it is in line with John 's answer below. — user3731811
– user3731811, Commented Jun 12, 2014 at 14:04

John C · Accepted Answer · 2014-06-12 13:23:45Z

2

I think you will find that it's actually there but you might not be able to see it...

Try something like this:

e = Company.select("companies.*, users.name as user_name").joins(:users)
e.each do |c|
   puts "company: #{c.name}, user: #{c.user_name}"
end

Output:

rails runner junk.rb
company: aaaaa, user: user 1
company: bbbbb, user: user 2
company: aaaaa, user: user 3

answered Jun 12, 2014 at 13:23

John C

4,4262 gold badges21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3731811 Over a year ago

@John, thanks a lot. I was really getting frustrated by this issue. It is exactly as you said, the way I did the query was allright, I just did not realise that the response one gets in Rails console does not really reflect the result of the query that is stored in the variable.... Seems I'm still at the bottom of the Rails learning curve.

John C Over a year ago

It just runs a script under a rails environment @arup

John C Over a year ago

It has caught me before too @user. For some reason the other columns are ignored by inspect, but they are there!

Arup Rakshit Over a year ago

Can I run any .rb file inside rails env ?

John C Over a year ago

Yes you can use it to run cron jobs.

Collectives™ on Stack Overflow

rails query for attributes from different tables/models

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related