Association JOIN/combine on array column

#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