valibuk.net

Last week I wrote a post how to use migrations to enter initial (or test) data into one table. Now I will show how does it work for two tables with a join.

Firstly, we will create the second model Employee; the User model defined in the previous post is the first model.

The Employee model represents the domain specific part of person details such as first name, surname and date of birth; the User model represents the technically specific part such as login name and password.

Two Tables

Create the second Employee model:

ruby script/generate model employee

I do not include the output, because it is very similar to the output for the User model.

The generated contains the default structure:

class CreateEmployees < ActiveRecord::Migration
  def self.up
  end

  def self.down
  end
end

We will add the above mentioned domain specific data such as first name, surname and date of birth.

Of course, we have to somehow save the relationship between the two models too. This is done by adding a column of the integer type that should be named model_id, where the model prefix is a name of the referenced model — in our case the name of the coloumn is user_id.

The modified 003_create_employees.rb migration looks like:

class CreateEmployees < ActiveRecord::Migration
  def self.up
    create_table :employees do |t|
      t.column :user_id,            :integer, :null => false

      t.column :first_name,         :string, :limit => 60, :null => false
      t.column :surname,            :string, :limit => 60, :null => false
      t.column :date_of_birth,      :date
      t.column :note,               :text
    end
  end

  def self.down
    drop_table :employees
  end

The Employee model, stored in the app/models/employee.rb file, is quite simple:

class Employee < ActiveRecord::Base
  belongs_to              :user
  validates_presence_of   :first_name,
                          :surname
end

Line 2 defines the join between the two models. Lines 3 and 4 says that the first name and surname are mandatory.

It is also necessary to add information about the join to the User model:

class User < ActiveRecord::Base
  has_one :employee
  #the rest of the source code
end

When I started programming in RoR, I was suprised that it is necessary to declare a join between two models in both models. In EJB 3 (in 2.x too) it is enough to write the @OneToOne annotation for a getter method (for EJB 2.x in a descriptor file).

When the join is defined in the RoR models, the database does not know about it. If tables are not be used by another application then it is up to you if your database should know about it. A join between two tables is expressed by a foreign key.
To add a foreign key we have to alter the Employee migration (lines 11, 12 and 13 were added):

class CreateEmployees < ActiveRecord::Migration
  def self.up
    create_table :employees do |t|
      t.column :user_id,            :integer, :null => false

      t.column :first_name,         :string, :limit => 60, :null => false
      t.column :surname,            :string, :limit => 60, :null => false
      t.column :date_of_birth,      :date
      t.column :note,               :text
    end

    execute "alter table employees " +
            "add constraint fk_employee_user " +
            "foreign key (user_id) references users(id)"
  end

  def self.down
    drop_table :employees
  end

The added lines execute an SQL code that adds the fk_employee_user foreign key (in general fs_this-model_referenced-model) that is stored in the user_id column and it references the id column in the user table — in other words: user_id column will contain a value of the id column from the user table. (The id column is added automatically by RoR. It is a primary key of a table. The employee table contains also one.)

It looks somehow complicated, doesn’t it? Well, you do not have to use it. It adds a double check if your data is correct. It will be checked by the RoR framework and by the database (that is a robust and well tested application used by many users).
A foreign key should be added when tables are accessed by another application, you execute additional SQL statements or your data is important for you.

And finally, let’s create a migration that will add some data to the Employee model:

script/generate migration add_employee_ondrej

It will generate the 004_add_employee_ondrej.rb migration with the default content:

class AddEmployeeOndrej < ActiveRecord::Migration
  def self.up
  end

  def self.down
  end
end

To insert data we need to call the constructor of the Employee model in the up method. To fill out fields like first name is easy, but how to set a value for the user_id field? We can check a value of the id column in the user table and use it :), but there is a better solution: we can access the User model and use the automatically created find_by_login_name method (RoR automatically creates a find_by_field finder method for every field):

User.find_by_login_name("ondrej").id

This returns a value of the id field of the ondrej user.

Let’s use the finder method and fill out also other fields in the 004_add_employee_ondrej.rb migration:

class AddEmployeeOndrej < ActiveRecord::Migration
  def self.up
    Employee.create(
      :user_id => User.find_by_login_name("ondrej").id,
      :first_name => "Ondrej",
      :surname => "Jaura",
      :date_of_birth => "1978-01-01".to_date,
      :note => "A diligent and committed employee.")
  end

  def self.down
    Employee.delete_all
  end
end

In the down method we delete all employees.

Now we execute the two new migrations:

rake db:migrate

The output should look like:

== CreateEmployees: migrating =================================================
-- create_table(:employees)
NOTICE:  CREATE TABLE will create implicit sequence "employees_id_seq" for serial column "employees.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
   -> 0.1011s
-- execute("alter table employees add constraint fk_employee_user foreign key (user_id) references users(id)")
   -> 0.0065s
== CreateEmployees: migrated (0.1081s) ========================================

== AddEmployeeOndrej: migrating ===============================================
== AddEmployeeOndrej: migrated (0.1136s) ======================================

Conclusion

It was shown how to use migrations for creating a model and (particularly) for inserting initial or test data to created models. The tutorial was divided into two parts: how to insert data to one table and how to insert data to two tables with a join.

Does this tutorial help you? Let me know :)