Rails has_many based on a json array column

user_228370 0
Viewed 868

Is it possible to tell Rails to use a JSON column as the "backend" for a relation? I have an Article model, that stores comment ids inside a JSON column Article#comment_ids:

class Article < ApplicationRecord
  def comments
    Comment.where(id: comment_ids)
  end
end

class Comment < ApplicationRecord
end

Article.first.comment_ids
=> [1,2]

Article.first.comments
=> [#<Comment:0x00007f4d7cff7c08 id: 1>,#<Comment:0x00007f4d7cff7c08 id: 2>]

Is there any way to replace this code

def comments
  Comment.where(id: comment_ids)
end

with a

has_many :comments # somehow reference #comment_ids

Background: Other parts of my application use eager loading and stuff like article.association(:comments).loaded?. And because it's not a rails relation, this doesn't work for the comments relation.

mysql-json ruby-on-rails mysql
1 Answers

No, its not possible. ActiveRecord is built around the relational model with tables that related to other tables. Querying JSON/JSONB/Array columns requires the use of completely different operators and they are not polyglot. They also have little to no advantages over a traditional join table:

  • JSON/Array columns cannot be used as foreign keys. So the database can't guarentee referential integrity.
  • A join table can have a highly effective compound index whereas you can have GIN indexes on a JSON/array column but you still have to traverse the whole thing to join the other table.
  • You're violating First normal form (1NF) by placing multiple values in a single column.
  • The queries with a JSON/array column are going to be hideous and unreadable. Joins are also faster then you think.
user_5448250
Related