SQL : Insert with Select inside

Issues
1

Hello everyone, I am creating a side project with ROM for my World of Warcraft Classic guild :wink:

tl;dr: How can I insert a loot into database with SELECT query to find the correct foreign-keys

I have to import RCLootCouncil json exports that looks like this :

        Data format
        {
            player: String, # Kaiiur
            date: String, # 26/8/20
            time: String, # 23:23:32
            itemID: Integer, # 21664
            itemString: String, # item:21664::::::::60
            response: String, # +1 need
            votes: Integer, # 2
            class: String, # PALADIN
            instance: String, # Temple d'Ahn'Qiraj-40 joueurs
            boss: String, # Fankriss l'Inflexible
            gear1: String,
            gear2: String,
            responseID: String, # 1
            isAwardedReason: String, # false
            rollType: String, # normal
            subType: String, # divers
            equipLoc: String, # Cou
            note: String,
            owner: String
        }

I have extracted each data that I needed for my domain into hash for further database insert

          characters = {}
          equipments = {}
          loots = []

          data.map do |row|
            character_name = row['player'].split('-').first
            loot_date = "#{row['date']}:#{row['time']}"

            characters[character_name] = {
              name: character_name,
              klass: row['class'],
              created_at: current_time,
              updated_at: current_time
            }

            equipments[row['itemID']] = {
              name: nil,
              image: nil,
              item_id: row['itemID'],
              created_at: current_time,
              updated_at: current_time
            }

            loots << {
              wish: row['response'],
              date: DateTime.strptime(loot_date, '%d/%m/%y:%H:%M:%s'),
              nb_vote: row['votes'],
              created_at: current_time,
              updated_at: current_time,

              identifiers: { # here an attempt was made to keep the relation data.
                character_name: character_name,
                item_id: row['itemID']
              }
            }
          end

The data is then inserted :
(don’t judge my PoC, this will be refactored when it works :smiley:)

          character_repo.characters.dataset.insert_conflict(
            constraint: :characters_name_key,
            update: {
              klass: Sequel[:excluded][:klass],
              updated_at: Sequel[:excluded][:updated_at]
            }
          ).multi_insert(characters.values)

          equipment_repo.equipments.dataset.insert_conflict(
            constraint: :equipments_item_id_key,
            update: {
              updated_at: Sequel[:excluded][:updated_at]
            }
          ).multi_insert(equipments.values)

Great, I have my characters and my item that are upserted, now it’s time to insert loots (no need to upsert)

As always, I create an SQL query that will looks like the final query I want to build from my ORM (here ROM)

INSERT INTO loots (wish, date, nb_vote, created_at, updated_at, character_id, equipment_id)
VALUES ('1', TO_DATE('26/08/2020', 'DD/MM/YYYY'), 2, TO_DATE('26/08/2020', 'DD/MM/YYYY'), TO_DATE('26/08/2020', 'DD/MM/YYYY'), (SELECT id FROM characters WHERE name = 'Keily'), (SELECT id FROM equipments WHERE item_id = 19339))
;

Now that I have everything, I need to build the request from ROM, and I am blocked.

I have no idea how to do that. :astonished:

How can I insert a loot into database with SELECT query to find the correct foreign-keys

Thanks for your help !

Additional informations

My repositories

# frozen_string_literal: true

module Loot
  module Repos
    class CharacterRepo < ROM::Repository[:characters]
      struct_namespace ::Loot::Entities

      include Import['container']

      commands :create,
               use: :timestamps,
               plugins_options: {
                 timestamps: {
                   timestamps: %i[created_at updated_at]
                 }
               }

      def all
        characters.combine(loots: [:equipment]).to_a
      end
    end
  end
end

# frozen_string_literal: true

module Loot
  module Repos
    class EquipmentRepo < ROM::Repository[:equipments]
      struct_namespace ::Loot::Entities

      include Import['container']

      commands :create,
               use: :timestamps,
               plugins_options: {
                 timestamps: {
                   timestamps: %i[created_at updated_at]
                 }
               }

      def all
        equipments.to_a
      end
    end
  end
end
# frozen_string_literal: true

module Loot
  module Repos
    class LootRepo < ROM::Repository[:loots]
      struct_namespace ::Loot::Entities

      include Import['container']

      commands :create,
               use: :timestamps,
               plugins_options: {
                 timestamps: {
                   timestamps: %i[created_at updated_at]
                 }
               }
    end
  end
end

My relations

# frozen_string_literal: true

module Loot
  module Relations
    class Characters < ROM::Relation[:sql]
      schema(:characters, infer: true) do
        associations do
          has_many :loots
        end
      end
    end
  end
end


# frozen_string_literal: true

module Loot
  module Relations
    class Equipments < ROM::Relation[:sql]
      schema(:equipments, infer: true) do
        associations do
          has_many :loots
        end
      end
    end
  end
end


# frozen_string_literal: true

module Loot
  module Relations
    class Loots < ROM::Relation[:sql]
      schema(:loots, infer: true) do
        associations do
          belongs_to :characters, as: :character
          belongs_to :equipments, as: :equipment
        end
      end
    end
  end
end
2

hey, sorry but this is not supported. You can always use a custom query and run it via relation’s dataset though.