DEV Community

Eric
Eric

Posted on

Associating Distant Table with Active Record

With Active Record, database tables can be connected together through Active Record association, allowing us to easily work with our database. Consider the diagram below:

db diagram

By creating different associations between these tables, Active Record will give our models access to additional methods, making it much easier to work with our database. In the end, I'll be able to interact with my tables like so:

playlist = Playlist.first

p playlist.name # => "Very Best of the 90's"
p playlist.tracks.sample.name # => "Step By Step"
p playlist.albums.sample.name # => "Siamese Dream"
p playlist.artists.sample.name # => "Alanis Morissette"

p playlist.albums.sample
          .tracks.first
          .playlists.sample
          .artists.first.name # => "Fleetwood Mac"
Enter fullscreen mode Exit fullscreen mode

Setup

In the interest of making this article as short as possible, I won't go over how to setup Active Record or how to create the initial database tables & table models. To start off, I have a database schema that looks like so:

# db/schema.rb

# ...

  create_table "albums", force: :cascade do |t|
    t.string "name"
    t.datetime "release_data"
    t.string "img_url"
  end

  create_table "artists", force: :cascade do |t|
    t.string "name"
    t.integer "popularity"
    t.string "img_url"
  end

  create_table "playlists", force: :cascade do |t|
    t.string "name"
    t.string "img_url"
  end

  create_table "tracks", force: :cascade do |t|
    t.string "name"
    t.integer "duration_s"
  end
Enter fullscreen mode Exit fullscreen mode

Setting Up Associations

Notice the diagram at the beginning of this article. The "Playlists" table and "Artists" table are nowhere near close to each-other, but by using Active Record associations, we can directly access all the artists represented in a specific playlist by simply calling .artists on an instance of Playlist.

playlist = Playlist.first
artist = Artist.first

p playlist.artists.pluck(:name).uniq
  # =>  ["Post Malone", "Kid Cudi", "Mr. Probz", "Miley Cyrus",
  #       "Fleetwood Mac","The xx", "Elton John", "Wiz Khalifa"]

# It works both ways
p artist.playlists.pluck(:name)
  # => ["Oldies", "Classic Hits"]


Enter fullscreen mode Exit fullscreen mode

Quick Explanation for #pluck:

#pluck is very useful method, provided by Active Record. It return an array of values when called on a collection of records. apidock.com says this about #pluck:

"Use #pluck as a shortcut to select one or more attributes without loading a bunch of records just to grab the attributes you want."


I've created four models (so far) representing the Playlists, Albums, Artists, & Tracks tables.

model files

Now, I'll create the table associations for each model, starting with Artists. Let's look at our database diagram again:

db diagram

Thinking it through, it seems reasonable to say that each artist would have many albums. So inside our artist model, we can describe that association with a has_many macro, like so:

# app/models/artist.rb
class Artist < ActiveRecord::Base
  has_many :albums # <- plural
end
Enter fullscreen mode Exit fullscreen mode

And, inside our album model, we can reciprocate this association by using a belongs_to macro:

# app/models/album.rb
class Album < ActiveRecord::Base
  belongs_to :artist # <- singular
end
Enter fullscreen mode Exit fullscreen mode

Note - make sure to use the correct singular/plural form of the table name when creating the associations.

Continuing, each album has many tracks & each track belongs to a single album (in our case). Adding to our album model:

# app/models/album.rb
class Album < ActiveRecord::Base
  belongs_to :artist
  has_many :tracks
end
Enter fullscreen mode Exit fullscreen mode

And inside the track model:

class Track < ActiveRecord::Base
  belongs_to :album
end
Enter fullscreen mode Exit fullscreen mode

Before going any further, I want to check if what we have so far works. But, first, I need to add foreign-keys to my album & track models, by creating a new migration.

#db/migrate/20220402193754_add_foreign_keys_to_albums_and_tracks.rb
class AddForeignKeysToAlbumsAndTracks < ActiveRecord::Migration[6.1]
  def change
    add_column :albums, :artist_id, :integer
    add_column :tracks, :album_id, :integer
  end
end
Enter fullscreen mode Exit fullscreen mode

Now, I can retrieve all an artist's tracks by calling:

Track.joins(album: [:artist]).where(:artist => {:name => "Nine Inch Nails"})`
  # returns an array of all tracks by "Nine Inch Nails"
Enter fullscreen mode Exit fullscreen mode

Note - #joins & #where are finder methods, provided by Active Record, for querying a database. You can learn more about them here.

Everything is good, so far, let's continue.

Our last direct association is a different from the previous two. Continuing to think it through, each playlist has many tracks, but each track could also have many playlists. In order to represent this relationship, we need to create a new table to join together our tracks table & playlists table (see diagram above).

Creating a Join Table

In a new migration file, I'll put the following:

class CreatePlaylistTracksTable < ActiveRecord::Migration[6.1]
  def change
    create_table :playlist_tracks do |t|
      t.integer :playlist_id
      t.integer :tracks_id
    end
  end
end
Enter fullscreen mode Exit fullscreen mode

This will create the joins table to link our tracks & playlists tables together in a many-to-many relationship. Next, I need to create a new model for this table.

class PlaylistTrack < ActiveRecord::Base
  belongs_to :playlist
  belongs_to :track
end
Enter fullscreen mode Exit fullscreen mode

With that, my entire database is now connected. I can now chain together methods to make queries like...

artists = Artists.joins(album: [tracks: [:playlists]])
                 .where(:playlist => {:name => "Very Best of the 90's"})
Enter fullscreen mode Exit fullscreen mode

which should give me a list of artists represented in the playlist, "Very Best of the 90's".

I should now be able to retrieve any data I'll need from the database. But the query above is kinda ugly. I'd rather write something like this:

artists = Playlist.find_by(name: "Very Best of the 90's").artists
Enter fullscreen mode Exit fullscreen mode

In order to be able to make this call, I need to set up some more associations.

Wrapping Up Associations

To supercharge my database queries, I added a few more associations to my models:

# app/models/playlist.rb
class Playlist < ActiveRecord::Base
  has_many :playlist_tracks
  has_many :tracks, through: :playlist_tracks
  has_many :albums, through: :tracks
  has_many :artists, through: :albums
end

# app/models/track.rb
class Track < ActiveRecord::Base
  belongs_to :album
  has_one :artist, through: :album
  has_many :playlist_tracks
  has_many :playlists, through: :playlist_tracks
end

# app/models/album.rb
class Album < ActiveRecord::Base
  belongs_to :artist
  has_many :tracks
  has_many :playlists, through: :tracks
end

# app/models/artist.rb
class Artist < ActiveRecord::Base
  has_many :albums
  has_many :tracks, through: :albums
  has_many :playlists, through: :tracks
end
Enter fullscreen mode Exit fullscreen mode

With this setup, I can now make super simple queries like so:

artists = Playlist.find(10).artists.pluck(:name).uniq
Enter fullscreen mode Exit fullscreen mode

This will return a list of all the artists in a playlist. That's pretty cool.

Conclusion

And that's it. Now my queries are supercharged and I can write methods that work with this setup to make complex queries a breeze.
Thanks for reading!

Top comments (0)