#has_many

どうも！TAKUYAです！

今回は私もいまだに頭の中ごちゃごちゃしているRailsにおけるActive Recordのアソシエーション（model間の関連付け）についてまとめたものを記事としてあげておこうと思います。

アソシエーションとは

テーブル間のリレーションシップをモデルの上の関係として操作できるようにする仕組み。 アソシエーションを利用すると複数のテーブルにまたがるデータ操作もより直感的に利用することができる。

アソシエーションの種類

Railsでサポートされているアソシエーションは以下の6種類。

belongs_to

has_one

has_many

has_many :through

has_one :through

has_and_belongs_to_many

belongs_to

belongs_to関連付けを行なうと、他のモデルとの間に「1対1」のつながりを設定す…

Topics

Rails - building your own app

Rails workflow

ActiveRecord::Base - get a lot of free methods

Associations - writing associations between Model classes

MUC - Model View Controller Framework

Projects

Associations Exercises - ActiveRecord::Base

URL Shortener

Build a tool that will take an arbitrarily-long URL and retun a shortened, random URL to the user

Made it to the bonus, yay!

Partner: Yi-Ke Peng

Learnings

Rails syntax/flow

rails g migration MigrationName

rake db:migrate

Create models/singularized_tablename.rb for class SingularizedTableName

Use create! since it throws an error if it fails, whereas create fails quietly)

rails c - Test out models in Pry using Rails console

Exit and re-enter rails c frequently to keep environment up to date

Make sure Postgres app is open/running!

Need to include following in Gemfile to run Rails console:

group :development do gem 'pry-rails' end

lib/db/schema.rb - will auto-update to show you current schema of database tables (columns, datatypes, indices, constraints)

CreateMyNameTable - will automatically include create table syntax

SQL create statement syntax

def change create table :shortened_urls do |t| t.string :long_url, null: false t.string :short_url, null: false t.integer :user_id, null: false t.timestamps end add_index :shortened_urls, :user_id add_index :shortened_urls, :short_url, unique: true end

add_index - adding an index will speed up search time, but take up more memory. Add indices if you expect to search using that column a lot, but don't index everything

Adding uniqueness constraint - must do this using an add_index ... unique: true line

add_index :table_name, [:column1, :column2], unique: true - will add uniqueness constraint on the combination of column1:column2

t.timestamps - adds created_at & updated_at timestamps, always include

drop_table :table_name - drops a table

add_column :table_name, :column_name, null: false - add column, can add more constraints to end\

has_many & belongs_to syntax

has_many :name_of_method, class_name: :NameOfOtherClass, foreign_key: :name_of_fk_pointing_to_me, primary_key: :name_of_my_pk belongs_to :name_of_method, class_name: :NameOfOtherClass, foreign_key: :name_of_my_fk, primary_key: :name_of_pk_on_other_table

fk/pk for a has_many should match the fk/pk for the corresponding belongs_to association in the other class

Can think of association_name == method_name

Default values for has_many/belongs_to if you only write has_many :name_of_method

class_name: SingularVersionOfAssociaionName

foreign_key: classname_id / myclassname_id

primary_key: id

has_many :through / has_one :through

has_many :new_method_name, through: :association_name (2nd to last step), source: :method_to_be_called_on_what_through_returns

has_one :through` - same as has_many :through, but only returns the first row of data

find_by_sql(sql_query) - use when ActiveRecord's built in SQL-esque methods are not effective, the query you need is more complicated. Can use heredocs to write sql_query

Rails Naming Conventions

DB tables - plural snake_case

Models - singular CamexlCase

Associations/methods - snake_case

Controllers - plural CamelCaseController

Views - snake_case mappings of controller action

I always had serious doubt on how these relations were enforced in Active Record when I first attempted to learn rails. Until today when I have some basic knowledge of SQL that I finally have a clear picture of what these Rails relation connections are built.

has_many and belongs_to:

This lovely couple is probably the most heavily used relation in many rails apps. Take a simple blog as example: 

# User has_many :posts # Post belongs_to :users

When we have this relation setup, we will need two corresponding tables as below:

The posts table is where it houses the user_id as foreign key, which is used for connecting the ownership relationship between posts and users. We may want to add a post_id in users table for indexing purpose. But this is out of the topic that I would like to discuss today.

With this relationship properly setup, we will be able to do user.posts, post,user freely. I will try to explain slightly about how this works for to prepare for the has_many_through relation model.

When we write has_many :posts in our User model, we are really implicitly doing this:

has_many(:posts,         :class_name => ‘Post’,         :foreign_key => :user_id,        :primary_key => :id)

has_many method takes in :posts which will be translated in rails to the indexing keyword, or method, or simply an alias that we will use. So we can totally change it to a more verbal keyword of our choice, e.g. :articles. In this situation, we will be using it as article.user instead of post.user. When we don’t specify the class name, foreign_key and primary_key, rails will recognizing the class name by signalizing and capitalizing posts, then look for a foreign key based on the current model name (which is User in our case). The foreign_key really is referring to the foreign_key in the posts table.

On the other hand, the Post model would have this:

belongs_to(:user,                     # likewise, we can change :user to :author, or word of your choice.         :class_name => ‘User’,         :foreign_key => :user_id,        :primary_key => :id)

belongs_to method does the other side of the job, and it’s similar to what has_many is doing, except that the foreign_key here is referring to the foreign_key of the current model, Post’s tables posts.

has_many and belongs_to are just methods. I hope me converting it to a style that comes with parenthesis will help you understand what it actually is. They take the :post, or :user respectively as an argument, then an option hash (or keyword argument after ruby 2.0) that will specify the details of the relation.

When we do a user.posts, the SQL queries are:

SELECT * FROM users WHERE id = id # rails uses ? to escape and read the user id from the user instance, you can find the full query from terminal when you do this operation.

SELECT * FROM posts WHERE user_id = id # this id refers to the user’s id. Similarly, rails uses ? escape

When we do a post.user, rails would do something similar as above, but the opposite way. One general rule of thumb to understand is the foreign_key always exists in the table that belongs_to someone else (The owner doesn’t know what it owns. Its the belongs’ job to know why its owner is). 

With the above knowledge, I can very easily construct one more relation using these two existing models. Let’s say some posts are series stories and they have a previous post. What I can do is add another column in the posts table, and call it prev_post_id. The new tables as below.

The prev_post_id is also a foreign_key column. But instead of pointing to another table, it points to the posts table’s id column. The rationale is all the posts are just posts, there’s no need to create a separate table. With this table setup, we are ready to build a previous post relation.

In the Post model, we will add the following codes:

belongs_to(:prev_post,                 :class_name => ‘Post’,                  :foreign_key => :prev_post_id,                 :primary_key => :id)

has_one(:next_post,             :class_name => ‘Post’,              :foreign_key => :prev_post_id,             :primary_key => :id)

Now, we get the freebies from rails where we can do post.next_post, and post.prev_post, which will allow us to navigate following the post sequence. This is very like a self join operation in SQL, but rails still does it in two separate queries (which is efficient in server memory usage, not the topic today, and I don’t know enough to discuss about it either). Feel free to set this up and you will be able to see how the queries look like in your rails console when you run the code.

The mystery of Active Record unfurled today as we dove straight into associations. Associations are a phenomenal tool that lets you use the power of SQL with the flexibility of Ruby and the magic of Rails.

The most common associations we used were has_many and belongs_to. Since we're just staring out with associations, we explicitly stated all of the information even if Rails would have picked up on it automatically. To give you a snippet - class User < ActiveRecord::Base

  has_many(     :submitted_urls,     class_name: "ShortenedUrl",     foreign_key: :submitter_id,     primary_key: :id   ) end

class ShortenedUrl < ActiveRecord::Base

  belongs_to(     :submitter,     class_name: "User",     foreign_key: :submitter_id,     primary_key: :id   ) end

This sets up a one to many relationship between our User and ShortenedUrl classes. Since our shortened_urls table has a foreign key pointing to to the users table, we use the belongs_to association in the ShortenedUrl class - this has counterintuitively been an easier way to think about it than 'A user can have many links,' especially as we moved on to more complicated has_many through associations.

While we still have to understand the SQL behind the magic of Active Record in order to use it, it clearly and quickly defines incredibly useful relationships.

When declaring an association between two ActiveRecord models, we must identify the nature of the relationship as well as the foreign and primary keys. Take the example below. In the migration file, I created two tables, employees and companies.

class CreateTables < ActiveRecord::Migration def change create_table :employees do |t| t.string :first_name t.string :last_name t.integer :company_id end create_table :companies do |t| t.string :name t.string :address end end end

Assuming each employee could only work for one company, I created a column in the employees table that holds a foreign key to the companies table. Company_id references the id column in the companies table, which is automatically generated. I define the relationship between employees and companies in the class definitions below.

class Employee < ActiveRecord::Base belongs_to( :company, :class_name => "Company", :foreign_key => company_id, :primary_key => id ) end class Company < ActiveRecord::Base has_many( :employees, :class_name => "Employee", :foreign_key => company_id, :primary_key => id ) end

An employee belongs to a company, and a company has many employees. The foreign key for this association is company_id, which sits in the employees table. The primary key is the id column in the companies table. By definition, the class that corresponds to the table that holds the foreign key is always on the belongs to end of the relationship. This makes sense because for each row in the employees table, there could only be one foreign key. On the other hand, there could be multiple rows that have the same foreign key value.

Last night (and into this morning) I was banging my head against an error when adding a search feature in my top secret app, to be named at a later date.

The search was built to look for players by name, but I wanted to include up to the last 3 teams or leagues the player had played on to help identify the correct player when search results included players of same name. 

Now players were not directly related to teams, but the relationship is created when the user writes a report on a player, during which they select a team or league. We also want to wait until the a report is publish (has a published timestamp)

So in the model:

class Player < ActiveRecord::Base has_many :scout_reports,-> {where('published IS NOT NULL')} has_many :teams, :through => :scout_reports

We are also interested in the most recent 3 most recent teams showing up in the search results, and to avoid duplicate team names appearing when multiple reports exist I tried adding uniq to the teams scope.

So it now looked like this:

class Player < ActiveRecord::Base has_many :scout_reports,-> {where('published IS NOT NULL').order('published DESC'} has_many :teams, ->{uniq}, :through => :scout_reports>

And that is when it failed.

The error looked like this:

ActiveRecord::StatementInvalid: PG::Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

After scouring the internets and trying variations of adding order and uniq, I found I needed to add a select statement to the teams scope that referenced the published attribute, as well as place the order using uniq. The finished and working product is as follows:

has_many :scout_reports,-> {where('published IS NOT NULL')} has_many :teams, ->{uniq.select("teams.*, scout_reports.published AS published").order('published DESC')},:through => :scout_reports before_create :format_names,:set_fullname

Another great link to learn and understand a complicated aspect of Rails.

This describes the difference between has_and_belongs_to_many and has_many :through, in simple way with tables and descriptions and everything.