Efficient way to return select columns from Rails 4 ActiveRecord nested query

Question

In my Rails 4 app, I have the following models:

class Person < ActiveRecord::Base
    has_many :addresses
  end

class Address < ActiveRecord::Base
  belongs_to :person
  belongs_to :city
end

class City < ActiveRecord::Base
  has_many :addresses
end

I'm using the :includes function to return query result into one variable:

Address.includes(:person, :city).where("person_id = 1")

It works as expected, except that I do not want the query to return every single column.

Here's what I've tried:

use select and specify table name and column names explicitly, e.g. "city.name", but Rails generates a big query with outer joins, that can be very costly, especially when there are lots of concurrent requests, so prefer a better solution.
don't want to hard code complete and raw SQL statements, because of maintenance issue later on
create a new "dummy" belongs_to relationship like in Address: belongs_to :city_select_columns, -> { select('name') }, :class => 'City', but that doesn't work (actually I'm not sure if that select is even supported, only came across documentation about where so far).
maybe define scope in City and Person? but I'm not sure how it should be defined or if it'd make sense to do it this way

Suggestions? Thanks

You said "It works as expected, except that I do not want the query to return every single column.", but which columns do you want ? — Baldrick
– Baldrick, Commented Apr 30, 2014 at 7:09

msanteler · Accepted Answer · 2014-04-30 15:10:50Z

3

Have you tried this?

class Person < ActiveRecord::Base
    has_many :addresses
    has_many :cities, :through => :addresses
end

class Address < ActiveRecord::Base
  belongs_to :person
  belongs_to :city
end

class City < ActiveRecord::Base
  has_many :addresses
end

Then:

Person.find(1).cities.pluck(:name)

Looks like this generates an INNER JOIN but with indexes it shouldn't be too costly?

answered Apr 30, 2014 at 15:10

msanteler

2,1831 gold badge17 silver badges21 bronze badges

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

2 Comments

jiax Over a year ago

Could not get pluck to return more than one columns properly. I ended up using a raw join SQL. Tried to leverage the API, but guess sometimes brute force is just more efficient :/

msanteler Over a year ago

I just tried ...pluck(:x,:y) and it returned and array of arrays: [[x1,y1],[x2,y2]]... Is this not sufficient?

Raj · Accepted Answer · 2014-04-30 06:59:44Z

1

Did you try select?

Address.select(<output_columns>).includes(:person, :city).where("person_id = 1")

answered Apr 30, 2014 at 6:59

Raj

23.1k14 gold badges105 silver badges148 bronze badges

2 Comments

jiax Over a year ago

that's what I meant by using joins, just updated my question. with .select('city.name, etc.'), Rails generates a big query involving outer joins in the back end, I prefer not using that :/

jiax Over a year ago

in my case, 3 queries are generated with :includes, no joins

jiax · Accepted Answer · 2014-05-01 06:02:45Z

0

Could not find a good query method using Rails' API, I ended up writing a raw inner join SQL, then call ActiveRecord::Base.connection.execute to run it.

answered May 1, 2014 at 6:02

jiax

2092 silver badges7 bronze badges

Collectives™ on Stack Overflow

Efficient way to return select columns from Rails 4 ActiveRecord nested query

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related