How to retrieve data from two tables using "joins"?

Question

I am beginner in Ruby-on-Rails and I started to investigate ActiveRecords. And I run into one problem.

I have two tables: todo_lists and users. The first one has following records: title, description, user_id, deadline. And the second one has only one record: name. I want to get a table that contains following records: title, description, name, deadline, i.e. combine two tables and put user instead of user_id. I try to solve this problem using joins:

TodoList.joins(:user).select("todo_lists.title, todo_lists.description, users.name, todo_lists.deadline")

But I get the new ActiveRecord::Relation without users.name:

[#]

This is my todo_list.rb and user.rb files:

class TodoList < ApplicationRecord
    belongs_to :user
end

class User < ApplicationRecord
    has_many :todo_lists
end

schema.rb file:

ActiveRecord::Schema.define(version: 2020_03_13_183504) do

  create_table "todo_lists", force: :cascade do |t|
    t.string "description", null: false
    t.integer "user_id", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.boolean "is_disabled"
    t.datetime "deadline"
  end

  create_table "users", force: :cascade do |t|
    t.string "name", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  add_foreign_key "todo_lists", "users"
end

Can anyone help me to solve this problem?

user_5025116 · Answer

If what you need is to access the user name from each object in the TodoList::ActiveRecord_Relation, you can use AS and give the proper name, which allows you to use it as a method from a single record on the ActiveRecord_Relation holding the user name:

TodoList.joins(:user).select("..., users.name AS user_name").first.user_name

Otherwise you can use as_json, which returns an array containing the data of every record as a hash, where each key is the column and the value, the corresponding values in the select statement:

TodoList.joins(:user).select("todo_lists.description, users.name").as_json
# [{"id"=>nil, "description"=>"1st", "name"=>"user1", ...},
#  {"id"=>nil, "description"=>"2nd", "name"=>"user2", ...}]

user_33866 · Answer

Sebastian Palma's is a good answer to the question as asked. It asked specifically how to solve this problem using joins.

That said, I think the traditional ActiveRecord approach to this problem is to use your models and associations.

You can add a user_name method to the TodoList model like this:

class TodoList < ApplicationRecord
  belongs_to :user

  def user_name
    user.name
  end
end

Now, any TodoList instance will return the name of its associated User regardless of how it was queried.

To avoid an n + 1 query scenario, you can use preload(:user) when querying TodoLists. For example, this code will make 2 queries to get the last 10 TodoLists and their associated Users.

TodoList.preload(:user).last(10)

How to retrieve data from two tables using "joins"?

2 Answers