Rails/postgres, 'foreign keys' stored in array to create 1-many association

Question

Can postgres arrays be used to create a one-to-many/has_many association in rails (4)? I am aware that a foreign key type array is not possible.

Example: A task has multiple assignees. Traditionally I would solve this using an association table: tasks->assignees->users. Using arrays, this would not be necessary as multiple 'foreign keys' could be stored.

The following query could then be used to get all tasks assigned to me:

select * from tasks where ? IN tasks.assignees

Answer 1

You won't be able to make Rails aware of this array and use it for associations.

But if you want quicker searching / filtering of Tasks assigned to users you can keep an array of User IDs in the Task object. Otherwise, you'd have to do a JOIN to find all tasks assigned to Alice, in your standard association table.

So the solution is to keep the association table but also duplicate the assignee User ID into the Task object and use that ID list for faster searching / filtering.

You'll need to hook into the after_create and after_destroy lifecycle for the assignee objects and insert new Assignee IDs into the Task record array. And then when an asignee is removed from a task update the array to remove the ID.

See Postgres docs for all the Array operators:

Something like this:

class Task < ActiveRecord::Base
    has_many :assignees, :dependent => :destroy
end

class Asignee < ActiveRecord::Base

    belongs_to :task
    after_create :insert_task_assignee
    after_destroy :remove_task_assignee

    # assumes that there is a column called assignee_id
    # that contains the User ID of the assigned person

    private

    def insert_task_assignee
        # TODO: check for duplicates here - before we naively push it on?
        task.assignee_list = task.assignee_list.push(assignee_id)
        task.assignee_list.save
    end

    def remove_task_assignee
        id_list = task.assignee_list
        id_list.reject! { |candidate_id| candidate_id == assignee_id }
        task.assignee_list = id_list
        task.assignee_list.save
    end

end

# find all tasks that have been assigned Alice & Bob
# this will require the `postgres_ext` gem for Ruby / Postgres array searching
tasks = Task.where.contains(:assignee_list => [alice.id, bob.id]).all