Rails has_many based on a json array column

Question

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.

https://guides.rubyonrails.org/association_basics.html#association-extensions is this something that might help you? — Eyeslandic, May 19 '20 at 09:00

score 0 · Answer 1 · answered May 19 '20 at 09:54

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.

We had huge problems using a join table, the model itself has about 25 million records and between 1-100 related models. So inserting and updating the join table lead often to lock wait timeouts. Storing them inside the record itself as a json array enabled us to perform the updates much faster. — 23tux, May 19 '20 at 10:54

Rails has_many based on a json array column

1 Answers1