Moving from direct has_many to has_many :through
Tags: ruby, rails
Published: 2021-01-08 11:06:42

A common Rails model association is a one-to-many, [`has_many` Association](https://guides.rubyonrails.org/association_basics.html#the-has-many-association).

This allows a Company to have many Users, but a User can only be in one Company. This looked like this in our app:

```ruby
class Company < ApplicationRecord 
  has_many :users, dependent: :destroy
end
```
```ruby
class User < ApplicationRecord 
  belongs_to :company
end
```

We also have a nice little extra `has_many` defined on the same relationship that is scoped to a particular type of user.

```ruby
class Company < ApplicationRecord 
  has_many :directors, -> { where(role: 'director') }, class_name: 'User'
end
```

### Changes

We wanted to allow one user to belong to many companies, and still keep one company can have many users. This is called a many-to-many association, and there a few ways to do this with Rails.
I think the most complex but most powerful is [`has_many :through` Association](https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association). Its quite well documented above and other places, but here is what I have working:

```ruby
class Company < ApplicationRecord 
  has_many :_company_users, dependent: :destroy
  has_many :users, through: :_company_users
end
```
```ruby
class User < ApplicationRecord 
  has_many :_company_users
  has_many :companies, through: :_company_users
end
```
```ruby
class CompanyUser < ApplicationRecord
  belongs_to :company
  belongs_to :user, dependent: :destroy
end
```

Note: I've declared the intermediate relationships with an underscore, eg `_company_users`, to denote they are internal plumbing and not for general usage.
That way when looking at the classes public methods they are not grouped with the normal accessors.
Although they can still be used as normal.


The `CompanyUser` is the join table, which can house other attributes as you need, like user permissions etc.

The difficulty came when addressing the scoped association. First query is to get the join table results for the scope (`_directors_users`), and then we need a named accessor that uses that to get the end result (`directors`). What was needed was the `source` to get the SQL correct :

```ruby
class Company < ApplicationRecord 
  has_many :_directors_users, -> { joins(:user).where(role: 'director') }, class_name: 'CompanyUser'
  has_many :directors, through: :_directors_users, source: :user
end
```

The schema now looks like this:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/sqbb3v2ilu90zuy8w142.png)


### Playground

I wanted to explore how `has_many` / with scope / `through` all worked, so for future me and anyone else interested, here are a few combinations and the resulting SQL.

```ruby
  has_many :directors_users, -> { where(id: '1') }, class_name: 'User'
  # SELECT "users".* FROM "users" WHERE "users"."company_id" = 1685 AND "users"."id" = 1

  has_many :directors_users, -> { where(role: 'director') }, class_name: 'User'
  # SELECT "users".* FROM "users" WHERE "users"."company_id" = 1686 AND "users"."role" = 'director'

  has_many :directors_users, -> { joins(:company_users).where(role: 'director') }, class_name: 'User'
  # SELECT "users".* FROM "users" INNER JOIN "company_users" 
  #                               ON "company_users"."user_id" = "users"."id"
  #                               WHERE "users"."company_id" = 1683 AND "users"."role" = 'director'"

  has_many :directors_users, -> { joins(:company_users).where(role: 'director') }
  # NameError: uninitialized constant Company::DirectorsUser

  has_many :directors_users, -> { joins(:company_users).where(role: 'director') }, class_name: 'CompanyUser'
  # ActiveRecord::ConfigurationError: Can't join 'CompanyUser' to association named 'company_users'; perhaps you misspelled it?

  has_many :directors_users, -> { where(id: '1') }, class_name: 'User', source: :users
  # SELECT "users".* FROM "users" WHERE "users"."company_id" = 1691 AND "users"."id" = 1

  has_many :directors_users, -> { joins(:user).where(role: 'director') }, class_name: 'CompanyUser'
  # SELECT "company_users".* FROM "company_users" INNER JOIN "users" 
  #                                               ON "users"."id" = "company_users"."user_id"
  #                                               WHERE "company_users"."company_id" = 1690 
  #                                               AND "company_users"."role" = 'director'"
  has_many :directors_users, -> { joins(:user).where('users.role': 'director') }, class_name: 'CompanyUser'
  # SELECT "company_users".* FROM "company_users" INNER JOIN "users" 
  #                                               ON "users"."id" = "company_users"."user_id" 
  #                                               WHERE "company_users"."company_id" = 1694 
  #                                               AND "users"."role" = 'director'
```