[Ruby] [Rails] Obtain a unique record from the table joined by the joins method of Active Record (Rails Tutorial Chapter 14)

3 minute read

Introduction

I encountered a bug that was not mentioned in the material while incorporating it into the Rails Tutorial. Summarize the cause of the bug and the solution.

What you can see in this article

・How to check the column names of joined tables by Active Record “joins” method

  • How to use the “distinct” method that uniquely retrieves duplicate records

    Reference materials

    ・Rails tutorial Https://railstutorial.jp/

  • Rails Guide Active Record association Https://railsguides.jp/association_basics.html ・[Rails] How to check the contents of the database [Cloud9] Https://shuheitakada.com/rails-database-check
  • Display column name as header when displaying the result of SELECT statement (.headers command) Https://www.dbonline.jp/sqlite/sqlite_command/index5.html ・About Rails distinct method Https://qiita.com/toda-axiaworks/items/ad5a0e2322ac6a2ea0f4

About the environment

Follow Rails Tutorial 6th Edition ・Use cloud9 ・Ruby 2.6.3p62 ・Rails 6.0.3

Learning motive

Rails Tutorial Chapter 14 14.3.3 Subselect Exercise 3 causes a bug. The purpose of the exercise is to display the users who followed using the joins method and the microposts associated with themselves as a feed. At first glance it looks good, image.png

When you post a micropost, image.png

No matter how I think about it, I enjoy too much. The cause is investigated while suppressing the feeling of not clearly enjoying.

Model association

The association of each model is as follows.

``


class User <ApplicationRecord
 has_many :microposts
 has_many :passive_relationships, class_name: "Relationship", foreign_key: "followed_id"
 has_many :followers, through: :passive_relationships, source: :follower
end

class Relationship <ApplicationRecord
 belongs_to :follower, class_name: "User"
end

class Micropost <ApplicationRecord
 belongs_to :user
end

# Copyright (c) 2016 Michael Hartl

The structure of each table is as follows. Rails Tutorial Chapter 14 Joins Related Diagram.png

The code that caused it

``

models/user.rb
def feed
 part_of_feed = "relationships.follower_id = :id or microposts.user_id = :id"
 Micropost.joins(user: :followers).where(part_of_feed, {id: id })
end

# Copyright (c) 2016 Michael Hartl

First, enter the console and check the SQL statement created by Rails.

``

rails_console

>>user.feed

SELECT * FROM "microposts" INNER JOIN "users" ON "users"."id" = "microposts"."user_id"
   INNER JOIN "relationships" ON "relationships"."followed_id" = "users"."id"
   INNER JOIN "users" "followers_users" ON "followers_users"."id" = "relationships"."follower_id"
   WHERE (relationships.follower_id = 1 or microposts.user_id = 1)
   ORDER BY "microposts"."created_at" DESC;

Next, check the structure and data of the table created by the joins method on the database console.

``

rails_dbconsole
Display the column name in the result of #SELECT
>>.header on

>>SELECT * FROM "microposts" INNER
  JOIN "users" ON "users"."id" = "microposts"."user_id"
  INNER JOIN "relationships" ON "relationships"."followed_id" = "users"."id"
  INNER JOIN "users" "followers_users" ON "followers_users"."id" = "relationships"."follower_id"
  WHERE (relationships.follower_id = 1 or microposts.user_id = 1)
  ORDER BY "microposts"."created_at" DESC LIMIT 5;

# Search result |~| means column omitted
id|content|user_id|~|id|name|~|id|follower_id|followed_id|~|id|name|~|
308|Enjoy Coding !!|1|~|1|Example User|~|50|4|1|~|4|Mr. Rey Lemke|~|
308|Enjoy Coding !!|1|~|1|Example User|~|51|5|1|~|5|Dr. Louisa Price|~|
308|Enjoy Coding !!|1|~|1|Example User|~|52|6|1|~|6|Charisse Stamm|~|
308|Enjoy Coding !!|1|~|1|Example User|~|53|7|1|~|7|Sang Metz IV|~|
308|Enjoy Coding !!|1|~|1|Example User|~|54|8|1|~|8|Robt Hamill|~|

Apparently, the follower_id is causing duplication in Microposts. By the way, from the fact that the number of duplication of Micropost and the number of its own followers match, It can be inferred that Followers are the cause of duplication.

In other words, in the case of the following follow relationships, Id: 1 Tanaka → id: 2 Suzuki Id:2 Suzuki → id:1 Tanaka Id:3 Sato → id:1 Tanaka Id:3 Sato → id:2 Suzuki The table shown below is created. By Micopost.where, the green frame and the red frame are extracted, and the red frame is doubled. RailsTuorial 14 Schematic diagram of the table created in Joins.png

``

models/user.rb
 # Green frame part
 relationships.follower_id = :id:

 # Red frame part
 microposts.user_id = :id

solution

It took me a long time to understand the cause, but if I knew the solution, I could solve it with only one word.

Use the distinct method

To get unique records without duplication, “distinct” method is used.

I will try incorporating it immediately.

``

models/user.rb
def feed
 part_of_feed = "relationships.follower_id = :id or microposts.user_id = :id"
 Micropost.joins(user: :followers).where(part_of_feed, {id: id }).distinct
end

# Copyright (c) 2016 Michael Hartl

image.png OK!!

write a test

Now is the time to write a test. So I will test Feed to make sure there are no duplicate microposts I posted.

test/integrationtest/microposts_interface_test.rb


 def setup
   @user = users(:michael)
 end

 test "should feed have microposts with uniqueness" do
   log_in_as(@user)
   get root_path

   #Post microposts
   content = "This micropost is only one!"
   post microposts_path, params: {micropost: {content: content }}
   follow_redirect!

   Confirming duplication of # feed
   assert_select'span.content', {:count=>1, :text=> "#{content}"}
 end

# Copyright (c) 2016 Michael Hartl