Joins Table for Task Relationships
Published: 2022-08-18 20:43:00

Many are familiar with ActiveRecord's built-in object relational mapping methods, as well as the way ActiveRecord lets you create one-to-many and many-to-many relationships with simple macros (:has_may, :belongs_to, and has_many, through:). However, there are many ways in which these built-in macros fall short. Say, for example, you were constructing a project management application that had three tables, projects, users and tasks. Simple enough. Tasks often times need to be done in a certain order. For example, say you have three tasks:
1. Fill the car up with Gas.
2. Google places to buy a birthday cake.
3. Drive to the bakery and purchase a cake.

It would be necessary to do these tasks in a specific order. The question is how to map this relationship where some tasks are parents of some tasks and children of others in ActiveRecord. That is what we will explore in this blog.

We start by generating our models. Let's go ahead and create tables for projects, users and tasks:

```CLI
rails g resource User username, bio, age, image

rails g resource Project name budget:integer duedate:date

rails g resource Task name user_id:integer project_id:integer

rails db:migrate
```

Ok, now that we have those tables generated, let's go ahead and map the relationships between the three in the way you are probably familiar with. Let's make a one-to-many relationship between users and tasks, and between projects and tasks where a user has many tasks and a project also has many tasks. Secondly, let's make it such that a project also has many users through tasks and a user has many projects through tasks.


```ruby
#in app/models/user.rb

class User < ApplicationRecord
    has_many :tasks
    has_many :projects, through: :tasks
end

#in app/models/project.rb

class Project < ApplicationRecord
    has_many :tasks
    has_many :users, through: :tasks
end

#in app/models/task.rb
class Task < ApplicationRecord
    belongs_to :project
    belongs_to :user
end


```

This will result in a database schema that looks like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kbk42behrdmtd0dlcs1.png)
 
This is great, but we need some data to view so we can start checking if our data relationships make sense. 

**if you haven't used faker before, you will need to install the gem, run `gem install faker` in your command line.

Now let's create some seed data for all three tables. In your `db/seed.rb` file, let's write the following algorithms:


```ruby
#source 10 random users using faker.
10.times do 
        User.create(
        username: Faker::Games::StreetFighter.character,
        bio: "Quote: #{Faker::Games::StreetFighter.quote}. Birthplace: #{Faker::Games::StreetFighter.stage}",
        image: Faker::Avatar.image,
        age: rand(100)
    end

#source 10 random projects using faker.
10.times do 
    Project.create(
        name: Faker::Games::Pokemon.move,
        details: Faker::Quotes::Shakespeare.hamlet_quote,
        duedate: Date.today+rand(10000),
        budget: rand(10000000))
end

#source 3 random tasks for every user in every project using faker.
# each user should have 3 tasks in each project. 10 users * 10 projects * 3 tasks = 300 tasks.

Project.all.each do |project|
    User.all.each do |user|

        (1..3).each do |e|
            Task.create(
                name: "task number #{e}",
                user_id: user.id,
                project_id: project.id
            )
        end
    end
end

```

This is great, but remember, we also want children and parent relationships mapped for the tasks table as well. Let's go ahead and create a new table by running `rails g resource Sequence parent_id child_id`

We know we want to tell ActiveRecord that every row in the sequence table has a parent_id and a child_id, both of which are foreign keys associated with instances of the Task class.

Looking back to our "buying a cake" analogy:
1. Fill the car up with Gas.
2. Google places to buy a birthday cake.
3. Drive to the bakery and purchase a cake.

We would want to map two different sequence relationships:
```
Sequence 1 | parent_task: Fill the car up with Gas | child_task: Google places to buy a birthday cake.

Sequence 2 | parent_task: Google places to buy a birthday cake | child_task: Drive to the bakery and purchase a cake.
```

If you read the documentation for the ActiveRecord association macros :belongs_to and :has_many at [guides.rubyonrails.org](https://guides.rubyonrails.org/association_basics.html) you will see that we can specify options for each macro. Namely, we can overwrite the typical naming conventions.

Now, overwriting naming conventions is usually not a good idea in ActiveRecord. But consider our dilemma. We want our sequence table to belong to the tasks table for both the parent_id foreign key AND the child_id foreign key. Using the naming conventions we would need two foreign keys with the same name...which ActiveRecord will not understand. Thankfully, we can specify a foreign key we would like to use and the class where ActiveRecord should look for that foreign key.

Let's update the sequence model to look like this:

```ruby
class Sequence < ApplicationRecord
    belongs_to :parent, :class_name => 'Task'
    belongs_to :child, :class_name => 'Task'
end
```

and the task model to look like this:

```ruby
class Task < ApplicationRecord
    belongs_to :project
    belongs_to :user

    
    has_many :child_sequences, :class_name => 'Sequence', :foreign_key => 'parent_id'
    has_many :parent_sequences, :class_name => 'Sequence', :foreign_key => 'child_id'


end

```

Here we are telling ActiveRecord that the sequences table has two foreign keys, parent_id and child_id, and that those foreign keys belong to instances of the Task class. 

Next we are telling ActiveRecord that tasks has many child_sequences and parent_sequences, and that they live in the Sequence class, and they can be identified through the foreign keys of parent_id and child_id respectively. 

This results in a database that looks like this:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sr2g6h8witmac0ukdthe.png)

Where the role of the sequences table is to map the relationship between different tasks.

This is working so far. However, one of the great features of following ActiveRecord's naming conventions is that it provides you with lots of built in methods that make your life easier. For example, because we followed the naming conventions when defining relationships between users and tasks, we can type `User.first.tasks` and get a list of all the tasks associated with that user. This, unfortunately, will not work with our sequences table because we broke the ActiveRecord naming convention. For example, we cannot type `Task.first.children_sequences` because ActiveRecord won't see a unique foreign key for children defined in the sequences table.

That being said, we are going to need to create our own Object Relational Mapping methods to quickly view all the children and parents of a specific task.

Let's update our task.rb to look like this

```ruby
class Task < ApplicationRecord
    belongs_to :project
    belongs_to :user

    
    has_many :child_sequences, :class_name => 'Sequence', :foreign_key => 'parent_id'
    has_many :parent_sequences, :class_name => 'Sequence', :foreign_key => 'child_id'

#instance method to return all the children of a task
    def children
        array_of_sequences = self.child_sequences
        array_of_child_tasks = []
        array_of_sequences.each do |e|
           child_task = Task.find_by id: e.child_id
           array_of_child_tasks.push child_task
        end
        array_of_child_tasks
    end

#instance method to return all the parents of a task

    def parents
        array_of_sequences = self.parent_sequences
        array_of_parent_tasks = []
        array_of_sequences.each do |e|
           parent_task = Task.find_by id: e.parent_id
           array_of_parent_tasks.push parent_task
        end
        array_of_parent_tasks
    end

end
```

Finally, we need to update our seed data to map the relationship between different tasks and store it in our sequences table. If you remember we have 3 tasks for every project. Let's just keep things simple and make each task a child of the task that comes before it. Let's go ahead and update our db/seed.rb to look like this:

```ruby
# This file should contain all the record creation needed to seed the database with its default values.
# The data can then be loaded with the bin/rails db:seed command (or created alongside the database with db:setup).
#
# Examples:
#
#   movies = Movie.create([{ name: 'Star Wars' }, { name: 'Lord of the Rings' }])
#   Character.create(name: 'Luke', movie: movies.first)

10.times do 
        User.create(
        username: Faker::Games::StreetFighter.character,
        bio: "Quote: #{Faker::Games::StreetFighter.quote}. Birthplace: #{Faker::Games::StreetFighter.stage}",
        image: Faker::Avatar.image,
        age: rand(100),
        password: 'fish')
    end


10.times do 
    Project.create(
        name: Faker::Games::Pokemon.move,
        details: Faker::Quotes::Shakespeare.hamlet_quote,
        duedate: Date.today+rand(10000),
        budget: rand(10000000))
end

# each user should have 3 tasks in each project.

Project.all.each do |project|
    User.all.each do |user|
        (1..3).each do |e|
            Task.create(
                name: "task number #{e}",
                user_id: user.id,
                project_id: project.id
            )
        end
    end
end

#for every task, define it as a parent and the next task as a child
Project.all.each do |project|
    project.tasks.each do |task|
        Sequence.create(
                        parent_id: task.id,
                        child_id: task.id + 1
                    )
    end
end

```

This way, if you open up your rails console and type `Task.first.children` you get `[#<Task id: 965, name: "task number 2", user_id: 202, project_id: 121, created_at: "2022-08-12 19:24:49.070099000 +0000", updated_at: "2022-08-12 19:24:49.070099000 +0000">] ```

Alternatively, you can type `Task.second.parent` you get `[#<Task id: 964, name: "task number 1", user_id: 202, project_id: 121, created_at: "2022-08-12 19:24:49.066551000 +0000", updated_at: "2022-08-12 19:24:49.066551000 +0000">] `

The reader should note that mapping relationships is a very deep, complex subject and that this blog only scratches the surface. What if we wanted not just the children of each task, but all the grandchildren and great grandchildren as well? My hope here was to provide a cursory introduction to the subject.

Happy coding!