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

Introduction

I ran into a bug that wasn't mentioned in the material while incorporating it into the Rails tutorial. Investigate the cause of the bug and summarize the solution.

What you can see in this article

-How to check the column name of the joined table by the "joins" method of Active Record -How to use the "distinct" method to uniquely acquire duplicate records

Reference material

・ Rails tutorial 　https://railstutorial.jp/ ・ Rails Guide Association of Active Record 　https://railsguides.jp/association_basics.html -[Rails] How to check the contents of the database [Cloud9] 　https://shuheitakada.com/rails-database-check -Display the column name as a header when displaying the result of the 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 use the joins method to display the microposts associated with the followers and themselves as a feed. It looks good at first glance,

When you post a micro post,

It's too much enjoyment no matter what. Investigate the cause while suppressing the feeling of not enjoying clearly.

Model association

The association of each model is as follows.

python

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.

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, go into the console and check the SQL statements 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 join method on the database console.

rails_dbconsole

#Display the column name in the SELECT result
>>.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 results|~|Means column omission
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, follower_id causes duplication in Microposts. By the way, because the number of duplicates of Micropost and the number of own followers match, It can be inferred that Followers are the cause of the duplication.

In other words, in the case of a follow-up relationship like the one below id: 1 Tanaka → id: 2 Suzuki id: 2 Suzuki → id: 1 Tanaka id: 3 Sato → id: 1 Tanaka id: 3 Sato → id: 2 Suzuki A table like the one shown below is created. By Micopost.where, the green frame and the red frame part are extracted, and the red frame part is doubled.

models/user.rb

 #Green frame part
 relationships.follower_id = :id: 

 #Red frame part
 microposts.user_id = :id

solution

I spent a lot of time trying to figure out the cause, but once I figured out the solution, I could solve it in just one word.

Use the distinct method

In order to get a unique record without duplication, the "distinct" method will be used.

I will try to incorporate 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

OK!!

Write a test

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

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

   #Posting a micro post
   content = "This micropost is only one!"
   post microposts_path, params: { micropost: { content: content }}
   follow_redirect!

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

#Copyright (c) 2016 Michael Hartl