The many_through_many plugin allow you to create an association using multiple join tables. For example, assume the following associations:
Artist.many_to_many :albums Album.many_to_many :tags
The many_through_many plugin would allow this:
Artist.plugin :many_through_many Artist.many_through_many :tags, [[:albums_artists, :artist_id, :album_id], [:albums_tags, :album_id, :tag_id]]
Which will give you the tags for all of the artist’s albums.
Let’s break down the 2nd argument of the many_through_many call:
[[:albums_artists, :artist_id, :album_id], [:albums_tags, :album_id, :tag_id]]
This argument is an array of arrays with three elements. Each entry in the main array represents a JOIN in SQL:
| first element |
represents the name of the table to join. |
| second element |
represents the column used to join to the previous table. |
| third element |
represents the column used to join to the next table. |
So the “Artist.many_through_many :tags” is translated into something similar to:
FROM artists JOIN albums_artists ON (artists.id = albums_artists.artist_id) JOIN albums_tags ON (albums_artists.album_id = albums_tag.album_id) JOIN tags ON (albums_tags.tag_id = tags.id)
The “artists.id” and “tags.id” criteria come from other association options (defaulting to the primary keys of the current and associated tables), but hopefully you can see how each argument in the array is used in the JOIN clauses. Note that you do not need to add an entry for the final table (tags in this example), as that comes from the associated class.
Here are some more examples:
# Same as Artist.many_to_many :albums Artist.many_through_many :albums, [[:albums_artists, :artist_id, :album_id]] # All artists that are associated to any album that this artist is associated to Artist.many_through_many :artists, [[:albums_artists, :artist_id, :album_id], [:albums_artists, :album_id, :artist_id]] # All albums by artists that are associated to any album that this artist is associated to Artist.many_through_many :artist_albums, [[:albums_artists, :artist_id, :album_id], [:albums_artists, :album_id, :artist_id], [:albums_artists, :artist_id, :album_id]], class: :Album # All tracks on albums by this artist (also could be a many_to_many) Artist.many_through_many :tracks, [[:albums_artists, :artist_id, :album_id]], right_primary_key: :album_id
Often you don’t want the current object to appear in the array of associated objects. This is easiest to handle via an :after_load hook:
Artist.many_through_many :artists, [[:albums_artists, :artist_id, :album_id], [:albums_artists, :album_id, :artist_id]], after_load: lambda{|artist, associated_artists| associated_artists.delete(artist)}
You can also handle it by adding a dataset block that excludes the current record (so it won’t be retrieved at all), but that won’t work when eagerly loading, which is why the :after_load proc is recommended instead.
It’s also common to not want duplicate records, in which case the :distinct option can be used:
Artist.many_through_many :artists, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_artists, :album_id, :artist_id]], distinct: true
In addition to many_through_many, this plugin also adds one_through_many, for an association to a single object through multiple join tables. This is useful if there are unique constraints on the foreign keys in the join tables that reference back to the current table, or if you want to set an order on the association and just want the first record.
Usage:
# Make all model subclasses support many_through_many associations Sequel::Model.plugin :many_through_many # Make the Album class support many_through_many associations Album.plugin :many_through_many