Self joins in rails through array field on other model

Question

We have 2 PostgreSQL tables as follows:

Keywords table: id, keyword (string)
Joins table: id, keyword_ids (integers array)

Now, I want to add associations to both Keywords and Aliases model as follows

class Keyword < ActiveRecord::Base
   has_many :joins
end

class Join < ActiveRecord::Base
   belongs_to :keywords foreign_key: 'keyword_ids'
end

How can I do that in Rails 4 and PostgreSQL 9.3?

this might be helpful: http://stackoverflow.com/a/12267745/291962, http://www.postgresql.org/docs/9.3/static/functions-array.html — Aboelnour, May 14 '15 at 23:25
@Aboelnour I found better articles here http://blog.plataformatec.com.br/2014/07/rails-4-and-postgresql-arrays/ and here http://blog.arkency.com/2014/10/how-to-start-using-arrays-in-rails-with-postgresql/ But they mentioned nothing about the associations! — Mahmoud M. Abdel-Fattah, May 14 '15 at 23:29

score 0 · Accepted Answer · answered May 16 '15 at 19:47

It seems that it can't be done with Rails associations, so I did the following model/class method

def alias_keywords
  AliasKeyword.select("keyword").joins("JOIN alias_joins ON alias_keywords.id = ANY(alias_joins.joins)").where("? = ANY(alias_joins.joins)", self.id).map{|x| x.keyword}
end

Now, I can easily, access AliasKeywords.first.alias_keywords

Self joins in rails through array field on other model

1 Answers1