Rails/Postgres, 'Foreign Keys' Stored in Array to Create 1-Many Association

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

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

Rails One-To-Many associations: Defining a foreign key field when scaffolding?

Its not necessary to have a foreign_key in article table but you should have. Code to generate the scaffold should be like below

rails g scaffold article vendor:references field1:datatype field2:datatype ...

It will generate a rails migration with the create statement for article table.
It will have a foreign_key i.e. vendor_id like below

class CreateArticles < ActiveRecord::Migration[5.1]
  def change
    create_table :articles do |t|
      t.references :vendor, foreign_key: true
      .....other fields

      t.timestamps
    end
  end
end

If you dont want forign_key, you can remove foreign_key from the migration

belongs_to :vendor will be automatically added in the article model.

Store multiple foreign key in one attribute

This is the wrong approach. Don't store the foreign keys in an array. Make a join table instead.

In the simplest arrangement, use a has-and-belongs-to-many relationship. This is for when you don't need to refer to the thing that connects the two models as a thing in its own right e.g. a car has many parts. If the connection is a real thing e.g. appointments connecting doctors and patients, then use a has-many-through relationship, where the join table has its own model.

The reason these approaches are better is that SQL will not be able to use proper JOINs to query across your data if you store the foreign keys in an array. Also, you are unable to specifically mark them as foreign keys, so the database will not be able to enforce referential integrity for you should you need this.

Rails method about foreign keys

Using ActiveRecord::reflect_on_all_associations and Array#reject you can filter all has_many association without :dependent key

Company.
  reflect_on_all_associations(:has_many).
  reject { |association| association.options[:dependent] }

To get table names you can use plural_name attribute

Company.
  reflect_on_all_associations(:has_many).
  reject { |association| association.options[:dependent] }.
  map(&:plural_name)

How to store foreign keys in the parent with a has_many relationship

The final answer, using Rails 4's support for PostgresQL's array column type for questions_list, was:

class Product < ActiveRecord::Base

  has_many :questions, :after_add => :add_to_questions_list, :after_remove => :remove_from_questions_list

  def add_to_questions_list(question)
    update_attribute :questions_list, questions_list << question.id
  end

  def remove_from_questions_list(question)
    update_attribute :questions_list, questions_list.reject{|i| i == question.id}
  end

  def versioned_questions
    questions_list.map{|id| Question.find(id)}
  end

end

Setting up Foreign Keys in Rails with Postgres

liker.likes.where(likee: liker) returns only a relation. You have to execute your query like this liker.likes.where(likee: liker).exists?

has_many :likes within your User model expects a column user_id within your table likes. With the code given I would think you have to change it to

class User < ApplicationRecord
  has_many :other_likes, foreign_key: :liker_id 
  has_many :likes, foreign_key: :likee_id 
end

belongs_to :connection is a bad name in my point of view. ActiveRecord::Base has a class method with the same name.
Maybe you will run into issues with such an association name some day.

---

Related Topics