CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-5
AnApprentice
AnApprentice

Reputation: 111030

How to join the Users table to a list of 100 Ranking Records to avoid 100+ extra user queries?

I have a ranking model with the following fields:

Ranking.rb
  t.string "job_type"
  t.integer "user_id"
  t.integer "ranking"
  t.integer "total_ranked"
  t.integer "points"

This table is populated by a Background job w ~100 records. I then want to have my API query and return the results of the records stored in ranking like so:

@rankings = Ranking.where(job_type: 'all_users').order('id ASC').select('id, user_id, ranking, points')

The problem is I do not have the associated User information per each ranking. Per each ranking, I want to user the user_id to find the User and get User.displayName, User.photo_url.

If I loop through @rankings and make a user query for each record that will be an extra 100+ queries.

How can I take @rankings and somehow include the associated user fields (User.displayName, User.photo_url)? Ideally with less database queries.

Upvotes: 1

Views: 94

Answers (1)

George
George

Reputation: 680

You want to use the active record :joins method. Assuming the users table also has a field called id and not user_id, this should work:

Ranking
  .select('rankings.id, rankings.ranking, rankings.points, users.displayName, users.photo_url')
  .joins('JOIN users ON rankings.user_id = users.id')
  .where(job_type: 'all_users')
  .order('id ASC')`

Edit: I couldn't find documentation for selecting values from multiple tables, just stack old overflow answers. So if that doesn't work, you could just do:

@rankings = Ranking.where(job_type: 'all_users').order('id ASC').select('id, user_id, ranking, points').includes(:user)

Which will load each Ranking with it's associated User all in one query using includes(which @thanh mentioned in his comment, he linked the docs too which are worth checking out). You can then loop through the ratings and get the displayName and photo_url without making extra database calls.

edit 2 :) According to this answer my first answer should work, but since :select returns an ActiveRecord::Relation object, it will only contain objects of the class you call :select on (in our case Ranking). The User columns will be available on those Ranking objects. It also looks like we'll need to alias those user columns, so something like this:

@rankings = Ranking
  .select('rankings.id, rankings.ranking, rankings.points, users.displayName as user_display_name, users.photo_url as user_photo_id')
  .joins('JOIN users ON rankings.user_id = users.id')
  .where(job_type: 'all_users')
  .order('id ASC')`

then access them like:

@rankings.first.user_photo_url #=> "https://flikr.com/whatever"

Upvotes: 4

Related Questions