Auto-populate SQL Table Using Active Record
Published: 2022-07-22 17:09:28

If you are reading this article then you probably are already familiar with the basics regarding SQL tables and how Active Record automatically maps ruby classes to sql tables. This was my level of understanding when I thought of an interesting challenge. What if you wanted to have a table that automatically populated rows based on the information provided in other SQL tables? How could you go about creating this?

The examples in this article relate to the martial art of Brazilian Jiu Jitsu. For those who are completely unfamiliar with the art, here is a basic run-down: Brazilian Jiu Jitsu is a wrestling art. One fighter wins by placing the other in a position where they either have to "submit" or risk being rendered unconscious or sustaining some injury. When one fighter submits in this way it is known as "tapping out." Brazilian Jiu Jitsu places a very large emphasis on formalized techniques and positions. For example, the most famous position is the "closed guard" where one fighter has the other wrapped in their legs like this:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t6a6b3esyrw84r4pu7q5.png)
When in this position, each fighter has numerous "attacks" at their disposal. Consequently, the other fighter has numerous possible "defenses" for each attack. If we were to try to map out all these techniques, we would get a multi-tiered nested data tree that looks something like this:

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

Where each position has many attacks, and each attack has many defenses. 

Now that we understand this basic structure, let's imagine for moment that we are a new Brazilian Jiu Jitsu student struggling to keep track of all the positions and attacks and defenses we are learning. We tried writing it all down in a note book, but then we remembered we are programmers, so this paper and pen format just won't do. Therefore, we need to create an SQL database using Active Record to keep track of what we are learning.

Our Active Record Schema looks something like this:

```ruby
create_table "attacks", force: :cascade do |t|
    t.string "name"
    t.string "result"
    t.string "notes"
    t.string "image"
    t.integer "position_id"
  end

  create_table "defenses", force: :cascade do |t|
    t.string "name"
    t.string "attack_id"
    t.string "notes"
    t.string "result"
    t.string "stage"
  end

  create_table "positions", force: :cascade do |t|
    t.string "name"
    t.string "notes"
  end
```

Additionally we have the following three ruby models:

```ruby
# in Position.rb
class Position < ActiveRecord::Base
    has_many :attacks
end

# in Attack.rb
class Attack < ActiveRecord::Base
    has_many :defense
    belongs_to :position
end

# in Defense.rb
class Defense < ActiveRecord::Base
    belongs_to :attack
end

```
 This is to say we have created 3 SQL tables, one for Positions, Attacks and Defenses. Additionally, we have created a ruby class to model each of these tables.

Ok, great! We can now easily store all of the techniques we are learning. But now let's say we want to study the techniques we are learning  and we want the computer to provide us with a possible sequence we can practice. For example, we want the program to tell us, "Start from closed guard, then practice the triangle choke attack and have your opponent defend by hiding his arm behind your leg." When we run through a scenario like this it is called a "sequence." Furthermore, say we didn't want to practice just one sequence, but over the course of time we wanted to practice ALL of the possible sequences.

This is where we return to our earlier challenge. How do we ask ruby to automatically generate a list of all the possible permutations of the techniques we have provided?

Firstly, consider the fact that as we add techniques, the number of potential sequences could get quite large. And in classic fashion, the best way to store large amounts of data is in an SQL table. So we are going to start by creating a new SQL table called "sequences" using Active Record. 

(Please note, I have not gone into detail about the set up for Active Record. If you need help getting everything set up check out this article: [use-activerecord-in-your-ruby-project](https://www.techcareerbooster.com/blog/use-activerecord-in-your-ruby-project)

To create a new migration we open the terminal, cd into our project directory and type `rake db:create_migration create_sequence_table`. Next, in the migration file, we need the following code:

```ruby
class CreateSequenceTable < ActiveRecord::Migration[6.1]
  def change

    create_table :sequences do |t|
      t.integer  :position_id
      t.integer  :attack_id
      t.integer  :defense_id

    end

  end
end

```

Our class is inheriting from ActiveRecord::Base. "create_table" is a method that comes along with Active Record. It uses a block parameter `|t|` and let's you specify as many columns as you like using the syntax `      t.integer  :position_id` or `t.string  :name` if your table is going to have a name column. In our case, since we know that there will be many sequences for every position, attack and defense, we have to specify a column with the integer datatype for each and use the naming convention `:position_id`. This tells ActiveRecord to map the 'id' field from positions, attacks and defenses tables to these columns respectively.

Now that we have that set up, we need to create an Active Record model to correspond with our 'sequences' table. Using Active Record convention, this file must be named "Sequence.rb" and have the following code:

```ruby
require_relative '../../config/environment.rb'

class Sequence < ActiveRecord::Base
    belongs_to :defense
    belongs_to :position
    belongs_to :attack

end
```

Next, we need to tell each of the other tables that they "have many" sequences in order for the tables to join.

```ruby
#in Position.rb
class Position < ActiveRecord::Base
    has_many :attacks
    has_many :sequences
end

#in Attack.rb
class Attack < ActiveRecord::Base
    has_many :defense
    has_many :sequences
    belongs_to :position
end

#in Defense.rb
class Defense < ActiveRecord::Base
    belongs_to :attack
    has_many :sequences
end

```

Let's take a step back now and remember what exactly it is that we want to do. We want to ask the program to populate the sequences table with all the possible permutations of positions, associated attacks and associated defenses.

Let's go ahead and define a few of these techniques to use in our example. 

```ruby
#create a position called "closed_guard"
Position.create name:"closed_guard"

#create an attack called "triangle_choke" that belongs to closed_guard
Attack.create name: 'triangle_choke', Position.find_by(name: "closed_guard").id

#create a defense called "posture_up" that belongs to the attack "triangle_choke"
Defense.create name: "posture_up", attack_id: Attack.find_by(name:'triangle_choke').id

#create a defense called "hide_arm" that belongs to the attack "triangle_choke"
Defense.create name: "hide_arm", attack_id: Attack.find_by(name:'triangle_choke').id

#create an attack called "omoplata" that belongs to closed_guard
Attack.create name: 'omoplata', Position.find_by(name: "closed_guard").id

#create a defense called "shoulder_roll" that belongs to the attack "omoplata"
Defense.create name: "shoulder_roll", attack_id: Attack.find_by(name:'omoplata').id

#create a defense called "duck_under_leg" that belongs to the attack "omoplata"
Defense.create name: "duck_under_leg", attack_id: Attack.find_by(name:'omoplata').id


#Create another position called "open_guard"
Position.create name:"open_guard"

#create an attack called "scissor-sweep" that belongs to open_guard
Attack.create name: 'scissor-sweep', Position.find_by(name: "open_guard").id

#create a defense called "post_with_arm" that belongs to the attack 'scissor-sweep'
Defense.create name: "post_with_arm", attack_id: Attack.find_by(name:'scissor-sweep').id

#create a defense called "post_with_leg" that belongs to the attack 'scissor-sweep'
Defense.create name: "post_with_leg", attack_id: Attack.find_by(name:'scissor-sweep').id

#create an attack called "over_the_leg_guard_pass" that belongs to open_guard
Attack.create name: 'over_the_leg_guard_pass', Position.find_by(name: "open_guard").id

#create a defense called "shrimp_out" that belongs to the attack 'over_the_leg_guard_pass'
Defense.create name: "shrimp_out", attack_id: Attack.find_by(name:'over_the_leg_guard_pass').id

#create a defense called "pull_closed_guard" that belongs to the attack 'over_the_leg_guard_pass'
Defense.create name: "pull_closed_guard", attack_id: Attack.find_by(name:'over_the_leg_guard_pass').id

```

This provides us with two positions, each with two attacks, and each of those attacks with two defenses. If we think about all the possible sequences using this data, we would have:

```
closed_guard > triangle_choke > posture_up
closed_guard > triangle_choke > hide_arm
closed_guard > omoplata > shoulder_roll
closed_guard > omoplata > pull_out_arm
open_guard > scissor-sweep > post_with_arm
open_guard > scissor-sweep > post_with_leg
open_guard > over_the_leg_guard_pass > Shrimp_out
open_guard > over_the_leg_guard_pass > pull_closed_guard
```

Now the question becomes, how do we get ruby to auto-populate the "sequences" table with the above mentioned sequences? This is where we get to make use of the fact that the Active Record model is really just a ruby class, meaning we can define class methods. Let's define one that looks like this:

```ruby
require_relative '../../config/environment.rb'

class Sequence < ActiveRecord::Base
    belongs_to :defense
    belongs_to :position
    belongs_to :attack

    def self.generate
        Position.all.each do |position| 
            position.attacks.each do |attack| 
                attack.defense.each do |defense|
                    self.create position_id: position.id, attack_id: attack.id, defense_id: defense.id
                end
            end  
        end
     end
end

``` 

We named our class method "generate". We are iterating through each defense, in each attack, in each position, and for every one, we are creating an instance of the Sequence class.

Now if we open our console, run Sequence.generate and checkout our sequences table using Sequence.all, we should see the complete list of all possible sequences.

```ruby
[#<Sequence:0x0000000110333f18
  id: 13,
  position_id: 33,
  attack_id: 1,
  defense_id: 141>,
 #<Sequence:0x0000000110333e28
  id: 14,
  position_id: 33,
  attack_id: 1,
  defense_id: 142>,
 #<Sequence:0x0000000110333d38
  id: 15,
  position_id: 33,
  attack_id: 2,
  defense_id: 143>,
 #<Sequence:0x0000000110333c70
  id: 16,
  position_id: 33,
  attack_id: 2,
  defense_id: 144>,
 #<Sequence:0x0000000110333ba8
  id: 17,
  position_id: 34,
  attack_id: 3,
  defense_id: 145>,
 #<Sequence:0x0000000110333ae0
  id: 18,
  position_id: 34,
  attack_id: 3,
  defense_id: 146>,
 #<Sequence:0x0000000110333
  position_id: 34,
  attack_id: 3,
  position_id: 34,
  attack_id: 3,
  defense_id: 146>,
 #<Sequence:0x0000000110333a18
  id: 19,
  position_id: 34,
  attack_id: 4,
  defense_id: 147>,
 #<Sequence:0x0000000110333950
  id: 20,
  position_id: 34,
  attack_id: 4,
  defense_id: 148>]


```

That's great, but we really need this in a format that is easy to read. Since we are going to be reading this data from a server, let's go ahead and build out the English version of this narrative in our application_controller.rb file by specifying a new route called "/sequences".

```ruby 
#when we make a fetch request to "/sequences"
get "/sequences" do

    #have narratives begin as an empty array
    narratives = []

    #for each sequence, build a json object containing a narrative by finding each position, attack and defense using their join key in the sequence table.
    Sequence.all.each do |sequence|
      narrative = {narrative: "Beginning in #{Position.find(sequence.position_id).name} position, one attacks with the #{Attack.find(sequence.attack_id).name} and the other defends with the #{Defense.find(sequence.defense_id).name} defense"}

      #and add that narrative to the narratives array
      narratives.push narrative

    end

    #convert narratives array to json and return it!
  narratives.to_json


  end
```
This way, when we run our server and make a get request to `http://localhost:9292/sequences`, we get this:

```json
// 20220720143954
// http://localhost:9292/sequences

[
  {
    "narrative": "Beginning in closed_guard position, one attacks with the triangle_choke and the other defends with the posture_up defense"
  },
  {
    "narrative": "Beginning in closed_guard position, one attacks with the triangle_choke and the other defends with the hide_arm defense"
  },
  {
    "narrative": "Beginning in closed_guard position, one attacks with the omoplata and the other defends with the shoulder_roll defense"
  },
  {F
    "narrative": "Beginning in closed_guard position, one attacks with the omoplata and the other defends with the duck_under_leg defense"
  },
  {
    "narrative": "Beginning in open_guard position, one attacks with the scissor-sweep and the other defends with the post_with_arm defense"
  },
  {
    "narrative": "Beginning in open_guard position, one attacks with the scissor-sweep and the other defends with the post_with_leg defense"
  },
  {
    "narrative": "Beginning in open_guard position, one attacks with the over_the_leg_guard_pass and the other defends with the shrimp_out defense"
  },
  {
    "narrative": "Beginning in open_guard position, one attacks with the over_the_leg_guard_pass and the other defends with the pull_closed_guard defense"
  }
]
```

Awesome! Here's one issue though. As it is set up now, the sequences table only populates when we run Sequence.generate. What happens if we learn a new defense for the triangle_choke and want to add it? How then would we make sure we also add the sequence that corresponds to that defense? 

One way we can do this is to create a new Class method called `create_with_sequence`. And if we remember, the "create" class method is coming from ActiveRecord::Base. Which means that within "create_with_sequence" we can call `self.create`. We can also write code that deletes all the entries in the sequence table and then regenerates the table, now with the new Defense we just created included in the sequences. In order to do this, let's modify our Defense.rb model file like this: 

```ruby 

require_relative '../../config/environment.rb'

class Defense < ActiveRecord::Base
    belongs_to :attack
    has_many :sequences

    def self.create_with_sequence args
        self.create args

        Sequence.all.each do |sequence|
            sequence.destroy
        end

        Sequence.generate
     end

end

```
Ok, so now let's create a new defense in the `rake console` like this:
```
Defense.create_with_sequence name: "tuck_arm_in_early", attack_id: Attack.find_by(name:'omoplata').id
```
Now we should see the new sequence showing up in our `http://localhost:9292/sequences`

```

    "narrative": "Beginning in closed_guard position, one attacks with the omoplata and the other defends with the tuck_arm_in_early defense"
  },

```

One cool thing about our data tree is that a new sequence is only defined when we create a new defense, so we only need to add this code in our Defense.rb file.

Let's make a few simple refactors to clean this up a bit. For starters, let's just include the code to completely clear the sequence table before rebuilding it in the Sequence model. Now our Sequence.rb looks like this:

```ruby
require_relative '../../config/environment.rb'

class Sequence < ActiveRecord::Base
    belongs_to :defense
    belongs_to :position
    belongs_to :attack

    def self.generate

        Sequence.all.each do |sequence|
            sequence.destroy
        end


        Position.all.each do |position| 
            position.attacks.each do |attack| 
                attack.defense.each do |defense|
                    self.create position_id: position.id, attack_id: attack.id, defense_id: defense.id
                end
            end  
        end


     end


end


```
And our Defense.rb file looks like this:

```ruby
require_relative '../../config/environment.rb'

class Defense < ActiveRecord::Base
    belongs_to :attack
    has_many :sequences

    def self.create_with_sequence args
        self.create args
        Sequence.generate
     end
  


end


```

Ok, so we have achieved what we set out to do, we created a table that automatically populates with narrative sequences using data from three other tables. 

Happy coding!