# Association JOIN/combine on array column

**URL:** <https://discourse.rom-rb.org/t/association-join-combine-on-array-column/300>\
**Category:** Issues\
**Created:** [January 22, 2019, 7:48am UTC](https://discourse.rom-rb.org/t/association-join-combine-on-array-column/300 "2019-01-22T07:48:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![joelvh](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/joelvh/32/139_2.png) [@joelvh](https://discourse.rom-rb.org/u/joelvh)\
**Post date:** [January 22, 2019, 7:48am UTC](https://discourse.rom-rb.org/t/association-join-combine-on-array-column/300/1 "2019-01-22T07:48:37Z")

</div>

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`.

```ruby
      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).

```ruby
      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

---

<div class="post-metadata">

**Author:** ![wuarmin](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/wuarmin/32/233_2.png) [@wuarmin](https://discourse.rom-rb.org/u/wuarmin)\
**Post date:** [March 10, 2022, 8:26am UTC](https://discourse.rom-rb.org/t/association-join-combine-on-array-column/300/2 "2022-03-10T08:26:22Z")

</div>

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.

```auto
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

```

```auto
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/](https://rom-rb.org/learn/core/5.2/associations/)