Rails / postgres，“外键”存储在数组中以创建1-many关联 [英] Rails/postgres, 'foreign keys' stored in array to create 1-many association

问题描述

可以使用postgres数组在rails中创建一对多/ has_many关联（4）吗？我知道外键类型数组是不可能的。

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.

示例：一个任务有多个受让人。传统上，我将使用关联表来解决此问题：task-> assignees-> users。使用数组，因为可以存储多个外键，所以这不是必需的。

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

推荐答案

您将无法

但是，如果您想更快地搜索/过滤分配给用户的任务，可以在其中保留一个用户ID数组。任务对象。否则，您必须做一个JOIN才能在标准关联表中找到分配给Alice的所有任务。

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.

所以解决方案是既保留关联表又保留将受让人用户ID复制到Task对象中，并使用该ID列表进行更快的搜索/过滤。

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.

您需要将 和 after_destroy 生命周期，并将新的受让人ID插入任务记录数组。然后，当从任务中删除受让人时，更新数组以删除ID。

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.

有关所有Array运算符，请参见Postgres文档：

See Postgres docs for all the Array operators:

类似这样的事情：

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 / postgres，“外键”存储在数组中以创建1-many关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！