ActiveRecord Join Query and select in Rails

Question

In my rails 4 application, a client (clients table) can have many projects (projects table). I have a column called name in each table. I am trying to write a join and then select which uses projects as the base table and clients as the lookup table. client_id is the foreign_key in the projects table:

I am writing my query as follows:

Project.joins(:client).select('projects.id,projects.name,clients.name')

I get the following response:

Project Load (0.6ms)  SELECT projects.id,projects.name,clients.name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"
=> #<ActiveRecord::Relation [#<Project id: 1, name: "Fantastico Client">]>

If I try to alias it like so:

Project.joins(:client).select('projects.id,projects.name,clients.name as client_name')

Then I get the following response:

Project Load (0.8ms)  SELECT projects.id,projects.name,clients.name as client_name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"
=> #<ActiveRecord::Relation [#<Project id: 1, name: "The Dream Project">]>

In either case, ActiveRecord looses one of the names as you can see from the above response. How should I be writing this query?

vee · Accepted Answer · 2014-03-10T22:58:53.243

102

If the column in select is not one of the attributes of the model on which the select is called on then those columns are not displayed. All of these attributes are still contained in the objects within AR::Relation and are accessible as any other public instance attributes.

You could verify this by calling first.client_name:

Project.joins(:client)
       .select('projects.id,projects.name,clients.name as client_name')
       .first.client_name

edited Mar 10 '14 at 22:58

answered Mar 10 '14 at 22:46

vee

38,255
7
74
78

3

If you do projects = Project.joins(:client).select('projects.id,projects.name,clients.name'). Then do project.first.attributes you'll see the missing columns. Like Vee said it only shows the "Project" models records. – Mark Davies Jan 10 '18 at 14:09

Ryan Taylor · Answer 2 · 2019-11-19T16:55:35.510

13

You can use :'clients.name' as one of your symbols. For instance:

Project.select(:id, :name, :'clients.name').joins(:client)

I like it better because it seems like Rails understands it, since it quotes all parameters:

SELECT "projects"."id", "projects"."name", "clients"."name"
FROM "projects"
INNER JOIN "clients" ON "clients"."id" = "projects"."client_id"

(I'm not 100% sure that's the exact SQL query, but I'm fairly certain and I promise it will use "clients"."name")

edited Nov 19 '19 at 16:55

answered Jun 01 '16 at 19:28

Ryan Taylor

12,559
2
39
34

1

I don't think this works because the SQL query doesn't have "as client_name" so "client.name" will override "project.name" attribute. – Jose Castellanos Jul 26 '17 at 14:49
I think you have to qualify your columns if you have 2 tables with the same field name. Otherwise you get an ambigous column error. E.G. Mysql2::Error: Column 'name' in field list is ambiguous – Mark Davies Jan 08 '18 at 14:15
@MarkDavies if you are sure, I can change my answer. Don't have a good Rails project to test it out on right now, but I think it does (surprisingly) work like I said. Let me know! – Ryan Taylor Jan 09 '18 at 22:44
Apologies Ryan, it does work but doesn't alias the columns. I am using MySQL, Ruby 2 and Rails 4.2. I tried it on a project using similar syntax (only had id as the same column in each table): Contact.select(:id, :'contact_statuses.id').joins(:contact_status) which produced the SQL: SELECT `contacts`.`id`, contact_statuses.id FROM `contacts` INNER JOIN `contact_statuses` ON `contact_statuses`.`id` = `contacts`.`contact_status_id`. – Mark Davies Jan 10 '18 at 14:00
@MarkDavies corrected it, but still haven't double checked it . I'm assuming you're right, would make sense! – Ryan Taylor Oct 01 '19 at 18:24

score 7 · Answer 3 · edited Feb 13 '20 at 09:41

7

To get both project table name and client name you can do like below query

Project.joins(:client).pluck(:name,:'clients.name')

edited Feb 13 '20 at 09:41

crusy

1,424
2
25
54

answered Feb 13 '20 at 07:59

hari

71
1
2

score 5 · Answer 4 · answered Mar 13 '14 at 15:10

your query don't looses any thing. Actually you have applied join on models and you have written Project.joins(:client) that why it is looking like. means It will hold Project related data as it is and associated data hold with alias name that you have given 'client_name' in your query.

if you use

Project.joins(:client)
   .select('projects.id project_id, projects.name projects_name,clients.name as client_name')

then it look like [#, #]

but it hold all the attribute that you selected.

nice, adding .to_json makes that super clear. I'd never have thought to check, since the data doesn't seem to be there otherwise. — Macpeters, Jul 15 '19 at 13:21

score 3 · Answer 5 · answered Oct 22 '20 at 15:29

Try This:

sql = Project.joins(:client).select(:id, :name, :"clients.name AS client_name").to_sql
data = ActiveRecord::Base.connection.exec_query(sql)

OUTPUT

[
  {"id"=>1, "name"=>"ProjectName1", "client_name"=>"ClientName1"},
  {"id"=>2, "name"=>"ProjectName2", "client_name"=>"ClientName2"}
]

ActiveRecord Join Query and select in Rails

5 Answers5

Linked