使用Postgres数组列用于Rails多对多关系

I've been hearing lately that some many-to-many and one-to-many relationships (without additional join data, eg user-membership-group) can be queried significantly faster in Postgres using array columns of id's rather than join tables see discussion for Node Postgres ORM.

我最近一直听说,在使用id的数组列而不是连接表的Postgres中,可以更快地查询一些多对多和一对多的关系(没有额外的连接数据,例如用户成员组)讨论Node Postgres ORM。

For a many-to-many relationship, choosing which table (or both) to maintain the relationship arrays would depend on which direction the query goes (users<->groups would need array columns on both tables, users->tag would only require the relationship to be maintained in an array column on the users table only.

对于多对多关系,选择哪个表(或两者)来维护关系数组将取决于查询的方向(用户< - >组需要两个表上的数组列,users-> tag只需要仅在users表的数组列中维护的关系。

Several questions:

• Are there any gems (Rails or otherwise) that are using the new Postgres array column to maintain relationships?

是否有任何宝石(Rails或其他)使用新的Postgres数组列来维护关系?

• Does anyone have any benchmarks for comparing a simple join table vs array column on a bi-directional and one directional many-to-many relationships?

有没有人在双向和单向多对多关系中比较简单的连接表和数组列有任何基准?

• To capture the performance improvements with Ruby, very basic functionality would look something like below. Aside from configuration for custom primary keys, method and class names, do you see any obvious improvement to be made from the code below?

要使用Ruby捕获性能改进,非常基本的功能将如下所示。除了自定义主键,方法和类名的配置之外,您是否看到下面的代码有任何明显的改进?

```

module ArrayRelationships

 def self.included(base)
   base.extend(ClassMethods)
 end

 module ClassMethods
   # association must be lower case, pluralized name of associated class
   def array_has_many(association)
    define_method(association) do 
      instance_name = association.singularize
      class_name = instance_name.titleize
      class_name.constantize.where(id: self.send("#{instance_name}_ids"))
    end
   end
 end
 end

 class User << ActiveRecord::Base
   include ArrayRelationships
   array_has_many :tags
 end

Of course, the users table would have to have the :tag_ids array field in the database. If we wanted to add the inverse relationship for Tag#users, we would simply add the db field, include ArrayRelationships, and array_has_many :users.

当然,users表必须在数据库中包含:tag_ids数组字段。如果我们想为Tag#用户添加反向关系,我们只需添加db字段,include ArrayRelationships和array_has_many:users。

1 个解决方案

#1

I haven't tried it yet, but appears that someone built a gem to support arrays for associations: https://github.com/marshall-lee/has_array_of. Copying from the README:

我还没有尝试过,但似乎有人构建了一个gem来支持关联数组:https://github.com/marshall-lee/has_array_of。从README复制:

How does it work?

Suppose we have a playlist that contains many videos. One video can be included in many playlists. It's a classic many-to-many situation but we implement it differently.

假设我们有一个包含许多视频的播放列表。一个视频可以包含在许多播放列表中。这是一个经典的多对多情况,但我们以不同的方式实现它。

# db/migrate/20141027125227_create_playlist.rb
class CreatePlaylist < ActiveRecord::Migration
  def change
    create_table :playlists do |t|
      t.integer :video_ids, array: true # adding array fields works only starting from Rails 4
      t.index :video_ids, using: :gin   # we add GIN index to speed up specific queries on array
    end
  end
end

# app/models/playlist.rb
class Playlist < ActiveRecord::Base
  has_array_of :videos  # by convention, it assumes that Post has a video_ids array field
end

# app/models/video.rb
class Video < ActiveRecord::Base
  belongs_to_array_in_many :playlists # optional
end

Now we can work with videos like with regular array. It will correctly proxy all changes to video_ids field.

现在我们可以使用常规数组等视频。它将正确代理对video_ids字段的所有更改。

playlist = Playlist.find(1)
playlist.videos = [video1,video2]  # playlist.video_ids = [1, 2]
playlist.videos[0] = video3        # playlist.video_ids[0] = 3
playlist.videos.insert(1, video4)  # playlist.video_ids = [3, 4, 2]
playlist.videos.delete_at(1)       # playlist.video_ids = [3, 2]
playlist.videos.pop                # playlist.video_ids = [3]
# ... and so on

video3.playlists
# => [playlist]