Rails: How to retrieve all model objects along with its foreign relation model with ActiveRecord?

Question

I have a model called Listing:

class Listing < ActiveRecord::Base
  belongs_to :user
end

I want to retrieve 100 listings along with each listing's user as a sub-hash within the listing hash, all with a single SQL call using ActiveRecord. This is how I'm doing it now directly with PostgreSQL:

def listings
  listings = ActiveRecord::Base.connection.execute("select row_to_json(t) as listing from (select *, row_to_json(users) as user from listings INNER JOIN users ON listings.user_id = users.id LIMIT 100) t")
  render json: {listings:listings,listing_count:Listing.count}
end

No looping over listings to retrieve their user, this is very time consuming. Thanks!

EDIT

I tried this as suggested but it is NOT returning the user:

  2.2.1 :012 >  Listing.joins(:user).limit(1)
      Listing Load (0.6ms)  SELECT  "listings".* FROM "listings" INNER JOIN "users" ON "users"."id" = "listings"."user_id" LIMIT 1
     => #<ActiveRecord::Relation [#<Listing id: 1, user_id: 1, deleted: false, rent: "$950", deposit: "$950", availability: "6/18/2015", min_duration: "11", male_count: "0", female_count: "1", longitude: "-73.9767700960917", latitude: "40.75831025967895", location: "Midtown Center", comments: "Sequi acidus utor sublime cito autus suasoria. Ips...", photos: "1.jpg, 2.jpg, 3.jpg", cat: false, dog: false, doorman: true, large_room: true, apartment: true, house: false, garden: true, personal_bathroom: false, dog_friendly: true, cat_friendly: false, laundry: false, gym: true, elevator: true, renovated: true, furnished: false, smoking: true, smoke_free: false, air_conditioning: false, utilities_included: true, four_twenty_friendly: false, gay_friendly: false, vegan_friendly: false, vegetarian: true, kosher: false, girls_only: false, guys_only: true, created_at: "2015-07-17 20:09:21", updated_at: "2015-07-17 20:09:21">]>

.joins won't work. That will just scope it to only listings with users, not include the User — Houen, Jul 29 '15 at 09:34

score 1 · Answer 1 · answered Jul 29 '15 at 10:04

Why a single SQL call? Is it enough that we avoid N+1 loading?

The time complexity is almost the same (O denotes a constant):

Single SQL call: 1*O(n) Two SQL calls: 2*O(n)

Here, O is just a different constant. In terms of runtime complexity that is acceptable, since it is still O(n).

With that as precondition:

It sounds like what you want to do is preload the User association. You can do that as such:

class Listing < ActiveRecord::Base
  belongs_to :user

  def self.to_hash_with_user
    relation = includes(:user)
    relation.map{ |record|
      hash = record.as_json
      hash['user'] = record.user.as_json
      hash
    }     
  end
end

And now you can use it as such

# Get some relation, e.g. Listing.limit(100)
# Because of lazy loading, this will not do a db call yet
listings = Listing.limit(100)
render json: listings.to_hash_with_user.to_json

For more on preloading records: http://blog.arkency.com/2013/12/rails4-preloading/

And if you want to clean up the JSON you return, and have much better structure and flexibility, I highly recommend this gem: https://github.com/rails-api/active_model_serializers

I'm seeing a 4x time performance with direct SQL call to postgres, which also renders the json internally with row_to_json(). Wondering if there's a way to get that with Rails/ActiveRecord, not sure if the serializer would do it? — etayluz, Jul 29 '15 at 14:46
What are the actual numbers you are getting? Performance on a db call is only important if it is not much lower than other slowing factors. If you are going from 0.6ms to 2.4ms, the latter is fully acceptable, unless you are building a high-speed reply for S2S calls. If it is for a user-facing website, that change is marginal. However, that is quite the performance increase. The serializer gem will not help with that. It is purely for speeding up new development. A well-crafted, single SQL call will generally always be faster than Rails — Houen, Jul 30 '15 at 07:19

score 0 · Answer 2 · edited May 23 '17 at 12:06

A left outer join should do it. It wouldn't return it as a subhash, but rather you'd get 100 hashes in your response, each one containing all the user and all the listing info.

info on how to do a left outer join in rails 4 here LEFT OUTER JOIN in Rails 4

So in your case

Listing.joins('LEFT OUTER JOIN users ON listing.user_id = users.id limit 100')

again not 100% what you're looking for but it does get all the info in one query.

Rails: How to retrieve all model objects along with its foreign relation model with ActiveRecord?

2 Answers2