Aggregate write on self referencing relation not setting foreign key

[rowanjt]

Describe the bug

When attempting to do a combine create on a table that has a parent/child association to itself, does not set the foreign key when inserting the tuples.

To Reproduce

require 'rom'
require 'rom-sql'

rom = ROM.container(:sql, 'sqlite::memory') do |config|

  config.gateways[:default].tap do |gateway|
    migration = gateway.migration do
      change do
        drop_table? :categories

        create_table :categories do
          primary_key :id
          foreign_key :category_id, :categories
          column :name, :string, null: false
        end
      end
    end

    migration.apply gateway.connection, :up

    gateway.connection.tap do |connection|
      connection.execute("INSERT INTO categories (id, name, category_id) VALUES (1, 'Books', NULL)")
      connection.execute("INSERT INTO categories (id, name, category_id) VALUES (2, 'Fiction', 1)")
      connection.execute("INSERT INTO categories (id, name, category_id) VALUES (3, 'Non-Fiction', 1)")
      connection.execute("INSERT INTO categories (id, name, category_id) VALUES (4, 'Fantasy', 2)")
      connection.execute("INSERT INTO categories (id, name, category_id) VALUES (5, 'Science-Fiction', 2)")
    end
  end

  class Categories < ROM::Relation[:sql]
    schema(infer: true) do
      associations do
        has_many :categories
      end
    end
  end

  config.register_relation(Categories)

end

category = {
  name: 'cat 1',
  categories: [{name: 'cat 1.1'}, {name: 'cat 1.2'}]
}

categories = rom.relations[:categories]
categories.combine(:categories).command(:create).call(category)
categories.to_a

Which results in...

 {:id=>1, :category_id=>nil, :name=>"cat 1"},
 {:id=>2, :category_id=>nil, :name=>"cat 1.1"},
 {:id=>3, :category_id=>nil, :name=>"cat 1.2"}

Expected behavior

I expect the following result...

 {:id=>1, :category_id=>nil, :name=>"cat 1"},
 {:id=>2, :category_id=>1, :name=>"cat 1.1"},
 {:id=>3, :category_id=>1, :name=>"cat 1.2"}

My environment

• Affects my production application: NOT YET - but required for product deployment
• Ruby version: 2.7.1
• OS: MAC OS 10.15.7

[solnic]

Thanks for reporting this!