Newbie question: populate a table field with identifiers making cross references to the same table

So I started a project to play with RoR using pokeapi.co. I don’t aim at implementing a full featured website, but more something like a minimal viable product for consulting some direct attribute of pokemons and their types, in a way that store data in the rails instance.

GitHub - psychoslave/poke: Let's have fun with PokeAPI and RoR is where I publish what I have done so far.

I was able to modelize pokemons that fit my aims, but have more issues with the Type model, which among other things reference itself through various “damage” relationships. As I wanted something as flat and simple as possible, I went a list of direct foreign keys from Type to Type for each kind of damage relationship.

Here is the corresponding migration:

class CreateTypes < ActiveRecord::Migration[6.1]
  def change
    create_table :types do |t|
      t.integer :pokeapi_id
      t.string :name
      t.string :generation_name
      t.references :double_damage_from, null: false, foreign_key:  { to_table: :types }
      t.references :double_damage_to, null: false, foreign_key:  { to_table: :types }
      t.references :half_damage_from, null: false, foreign_key:  { to_table: :types }
      t.references :half_damage_to, null: false, foreign_key:  { to_table: :types }
      t.references :no_damage_from, null: false, foreign_key:  { to_table: :types }
      t.references :no_damage_to, null: false, foreign_key:  { to_table: :types }
      t.string :move_damage_class_name
      t.string :moves_names

      t.timestamps
    end

    create_join_table :pokemons, :types do |t|
      t.index :pokemon_id
      t.index :type_id
    end
  end
end

The corresponding model:

class Type < ApplicationRecord
  has_many :double_damage_from_items, class_name: "Type", foreign_key: "double_damage_from_id"
  has_many :double_damage_to_items, class_name: "Type", foreign_key: "double_damage_to_id"
  has_many :half_damage_from_items, class_name: "Type", foreign_key: "half_damage_from_id"
  has_many :half_damage_to_items, class_name: "Type", foreign_key: "half_damage_to_id"
  has_many :no_damage_from_items, class_name: "Type", foreign_key: "no_damage_from_id"
  has_many :no_damage_to_items, class_name: "Type", foreign_key: "no_damage_to_id"
  has_and_belongs_to_many :pokemons
  #belongs_to :pokemon
end

And how I try to populate the table:

puts 'Dropping all existing type entries'
Type.delete_all
puts 'Importing types entries from PokeApi'
Type_count = PokeApi.get(:type).count
PokeApi.get(type: {limit: Type_count}).results.each do |wad|
  bib = wad.url.match(/(\d+)\/$/)[1]
  ens = PokeApi.get(type: bib)
  type = Type.create(
    id: ens.id,
    pokeapi_id: ens.id,
    name: ens.name,
    generation_name: ens.generation.name,
    double_damage_from_id: ens.damage_relations.double_damage_from.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    double_damage_to_id: ens.damage_relations.double_damage_to.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    half_damage_from_id: ens.damage_relations.half_damage_from.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    half_damage_to_id: ens.damage_relations.half_damage_to.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    no_damage_from_id: ens.damage_relations.no_damage_from.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    no_damage_to_id: ens.damage_relations.no_damage_to.map{|ens| ens.url.match(/(\d+)\/$/)[1]},
    move_damage_class_name: ens.move_damage_class.name,
    moves_names: ens.instance_variable_get(:@moves).map{|ens| ens.name}.sort,
  )
end

The issue I face is that I try to populate things like double_damage_from_id with an array of identifiers to other Type items, when it seems to expect a single identifier.

So is there a way to indeed keep things as flat as I was initially attempting to implement? Maybe pluralize some things might help Rails do some magic here? I actually tried that by adding _items suffixes in the model, just to test if I could then assign an array through the matching method, but it failed. And arguably I guess this would not align with Rails conventions on keeping method names aligned with db field names.

Maybe there is no other option, or at least no option that follow good practices, than create an other table, say damages(name:string, source:Type, target:Type)?

Thanks in advance for your feedbacks

So, I finally found time to dedicate to this, and I opted to a change of db schema. I turned to an implementation using multiple self-joined relationships, one per damage class.

So the related migration now include:

    damage_relationships = %i{double_damage_from double_damage_to
                              half_damage_from half_damage_to
                              no_damage_from no_damage_to}

    damage_relationships.each do |damage_relationship|
      create_table damage_relationship, id: false do |t|
        t.integer :subject_id
        t.integer :object_id
      end

      add_index(damage_relationship, [:subject_id, :object_id], unique: true)
      add_index(damage_relationship, [:object_id, :subject_id], unique: true)
   end

and model goes like this:

class Type < ApplicationRecord
  has_and_belongs_to_many :pokemons

  has_and_belongs_to_many :double_damage_from,
            class_name: "Type",
            join_table: :double_damage_from,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

  has_and_belongs_to_many :double_damage_to,
            class_name: "Type",
            join_table: :double_damage_to,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

  has_and_belongs_to_many :half_damage_from,
            class_name: "Type",
            join_table: :half_damage_from,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

  has_and_belongs_to_many :half_damage_to,
            class_name: "Type",
            join_table: :half_damage_to,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

  has_and_belongs_to_many :no_damage_from,
            class_name: "Type",
            join_table: :no_damage_from,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

  has_and_belongs_to_many :no_damage_to,
            class_name: "Type",
            join_table: :no_damage_to,
            foreign_key: :subject_id,
            association_foreign_key: :object_id

end

Related resources:

By the way, as the [Active Record Basics](Active Record Basics — Ruby on Rails Guides] guide recall:

While these column names are optional, they are in fact reserved by Active Record. Steer clear of reserved keywords unless you want the extra functionality. For example, type is a reserved keyword used to designate a table using Single Table Inheritance (STI). If you are not using STI, try an analogous keyword like “context”, that may still accurately describe the data you are modeling.

So, here luck was on my side as I didn’t have to implement a type attribute on the Pokemon class. The implementation do allow to make Pokemon.first.types.map{|type| type.name}, thanks to ActiveRecord::Associations::CollectionProxy.