Use Postgres Array columns for Rails for many to each other
I've heard lately that some relationships many-to-many
and one-to-many
(without additional join data, like user-membership-group) can be queried much faster in Postgres using id array columns rather than joining tables see discussion for Postgres Node ORM .
For a relationship, the many-to-many
choice of a table (or both) to maintain arrays of relationships will depend on the direction in which the query is going (for user groups <- column columns are needed on both tables, the users-> tag requires that the relationship is maintained only in an array column only in users table.
A few questions:
- Are there any gems (Rails or otherwise) that use the new Postgres array column to maintain relationships?
- Does anyone have tests for comparing a simple join table to an array column with bi-directional and unidirectional relationships
many-to-many
? - To achieve performance improvements with Ruby, very simple functions would look like the following. Apart from configuration for custom primary keys, method names, and class names, do you see any obvious improvement from the code below?
``,
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 must have an array field :tag_ids
in the database. If we wanted to add feedback for Tag # users, we would simply add a db field including ArrayRelationships and array_has_many :users
.
source to share
I haven't tried it yet, but it seems like someone has created a gem to support arrays for associations: https://github.com/marshall-lee/has_array_of . Copying from README:
How it works?
Let's say we have a playlist containing many videos. One video can be included in many playlists. This is a classic many-to-many situation, but we will 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 it videos
as with a regular array. It correctly proxies all changes to the 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]
source to share