Association JOIN/combine on array column

Issues
1

I’ve been spending a lot of time trying to get a join on an array column to work, and I’ve got the query, but can’t get the combine to work. I’m using ROM SQL.

I’ve got a relation called requests which has an array column attachment_ids.

      class Requests < ROM::Relation[:sql]
        schema(:requests, infer: true) do
          associations do
            has_many :request_attachments, as: :attachments, view: :for_requests_as_attachments, override: true, combine_keys: { attachment_ids: :id }
          end
        end

I’ve got an attachments relation with a view that does a CROSS JOIN (lifted from a Sequel Model extension).

      class Attachments < ROM::Relation[:sql]
        schema(:request_attachments, infer: true)

        def for_requests_as_attachments(assoc, requests)
          array_join(assoc, requests, relation: self.requests, foreign_key: :attachment_ids, primary_key: :id)
        end

        def array_join(assoc, other, relation:, foreign_key:, primary_key: :id)
          # unnest array of IDs
          join_table_name = :"_unnested_"
          join_table_column = :id
          unnested_table = Sequel.function(:unnest, relation[foreign_key]).as(join_table_name, [join_table_column])
          unnested_id = Sequel[join_table_name][join_table_column]

          new(
            dataset.from(relation.name.dataset, :request_attachments)
                    .cross_join(unnested_table)
                    .where(unnested_id => self[primary_key], relation[relation.primary_key] => other.map { |item| item[other.source.primary_key] })
          )
        end
      end

The query works as expected, but requests.combine(:attachments) always returns an empty array.

Can someone help with how to properly combine these?

Thanks!

~Joel

2 Likes
2

Hello!
Here’s an example of how to do that. It is not specifically your example, but should be able to be solved analogously.

The example uses the PostgreSql-Support. There is a relation of users and a relation of roles. The user holds its roles as role_ids instead of a many_to_many-table.

module Orm
  module Relations
    class Users < ROM::Relation[:sql]
      schema(:users, infer: true) do
        associations do
          has_many :roles, override: true, view: :for_users, combine_key: :user_id
        end
      end
    end
  end
end

module Orm
  module Relations
    class Roles < ROM::Relation[:sql]
      schema(:roles, infer: true)

      def for_users(_assoc, users)
        join(:users) { |r|
          r[:users][:role_ids].any(r[:roles][:id])
        }.where { |r|
          r[:users][:id].in(*users.map { |f| f[:id] })
        }.select_append { |r|
          r[:users][:id].as(:filter_id)
        }
      end
    end
  end
end

The guide is very helpful https://rom-rb.org/learn/core/5.2/associations/