Associating Distant Table with Active Record
Published: 2022-04-14 18:02:52


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d0edxdqzf4hjgyizu83k.png)


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:

```rb
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"
```

## 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:

```rb
# 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
```

## 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`.

```rb
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"]


```

---

#### 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](https://apidock.com/rails/ActiveRecord/Calculations/pluck) 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4lzojk5cz1orhz8eza08.png)


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

![db diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d0edxdqzf4hjgyizu83k.png)


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:

```rb
# app/models/artist.rb
class Artist < ActiveRecord::Base
  has_many :albums # <- plural
end
```

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

```rb
# app/models/album.rb
class Album < ActiveRecord::Base
  belongs_to :artist # <- singular
end
```

_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:

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

And inside the track model:

```rb
class Track < ActiveRecord::Base
  belongs_to :album
end
```

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.

```rb
#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
```

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

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

---

_Note - `#joins` & `#where` are finder methods, provided by Active Record, for querying a database. You can learn more about them [here](https://guides.rubyonrails.org/active_record_querying.html)_.

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:

```rb
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
```

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.

```rb
class PlaylistTrack < ActiveRecord::Base
  belongs_to :playlist
  belongs_to :track
end
```

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

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

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:

```rb
artists = Playlist.find_by(name: "Very Best of the 90's").artists
```

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:

```rb
# 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
```

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

```rb
artists = Playlist.find(10).artists.pluck(:name).uniq
```

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!