custom rails has_many association ( through pg array )

Question

So basically I'd like to know if there's some common approach to define own association types. Some details:

I have a model conversations that has a PG array column user_ids. So, to retrieve user conversations I need to run: select conversations.* from conversations where USER_ID = ANY(conversations.user_ids)

Since finder_sql and it's friends are deprecated now, I would really like to know what would be the best way to implement this pseudo has_many association?

currently I just use the methods like:

 def conversations
   Conversation.where("#{id} = ANY (conversations.users)")
 end

So basically I'm thinking of implementing my own ActiveRecord::Associations::CollectionAssociation and would like to know if there're some good references or if you could advice where to start at

---

Answer 1

I suggest to normalize your model to have another table who keeps the relations between users and conversations for example, building a model like

UsersConversation

and your relations will be

class User < ApplicationRecord
  has_many :users_conversations, :dependent => :destroy
  has_many :conversations, :through => :users_conversations
end

class Conversation < ApplicationRecord
  has_many :users_conversations, :dependent => :destroy
  has_many :users, :through => :users_conversations
end     

Then you can easily do

#users for each specific conversation
@users = Conversation.find(1).users
#conversations for each specific user
@conversations = User.find(1).conversations

Unless you want to keep the same way or array, you can do it like

@users = User.where(:id => Conversation.find(1).user_ids)

if the user_ids stored as array like [1,10,20] So, it will work fine and return the users

Answer 2

You should re-structure it to the reference table where it holds the conversation_id and user_id, then you can use it the best practice from Rails (https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association)

If you still want to keep this structure I suggest:

User.where(id: conversion.user_ids) (conversion is an instance of the Conversation table)