Rails Active Record: How to join three tables?

Question

I've three tables as shown below:

Advertiser model:

class Advertiser < ActiveRecord::Base
 has_many :advertisers_account_groups

AdvertisersAccountGroup model

class AdvertisersAccountGroup < ActiveRecord::Base
 belongs_to :advertiser
 belongs_to :v2_account_account_group, class_name: 'V2Account::AccountGroup', foreign_key: 'account_group_id'

I wanna know which advertiser belongs to v2_account_account_groups and wanna get v2_account_account_groups.name

Desired Output:

What I tried;

Advertiser.where(media_type: "line").joins(advertisers_account_groups,v2_account_account_groups)

But it doesn't work

Answer 1

It looks to me that your current setup uses AdvertisersAccountGroup as a join table; therefore, I'd suggest using a has_many :through association.

To do this, you'd just need to switch up the models as follows:

class Advertiser < ActiveRecord::Base
  has_many :v2_account_account_groups, through: :advertisers_account_groups
  has_many :advertisers_account_groups
  ...
end

class V2Account::AccountGroup < ActiveRecord::Base
  has_many :advertisers, through: :advertisers_account_groups
  has_many :advertisers_account_groups
  ...
end

class AdvertisersAccountGroup < ActiveRecord::Base
  belongs_to :advertiser
  belongs_to :v2_account_account_group, class_name: 'V2Account::AccountGroup', foreign_key: 'account_group_id'
  ...
end

This will allow you to query against the advertiser as desired, i.e. advertiser.v2_account_account_groups.

However, this association is a many-to-many between advertisers and v2 account groups as is - therefore, you won't be able to call advertiser.v2_account_account_groups.name as advertiser.v2_account_account_groups returns a collection rather than a single record.

You could use advertiser.v2_account_account_groups.map(&:name) (to get an array of all groups' names) or advertiser.v2_account_account_groups.first&.name, but it sounds as if you might need to restructure the data if an advertiser should have just the one v2 account group.

Does that make sense and sound like what you're looking for? Let me know if you've any questions.

---

Edit:

Based on your comment, I think you should be able to construct a query as follows:

Advertiser.includes(advertiser_account_groups: : v2_account_account_group)
          .where(advertiser_account_groups: { v2_account_groups: { name: "something" } })

Does that sound like what you're looking for?

A couple of things to note:

• when referencing the associations in the includes, you want to use the association name
• however, when plugging these into the where clause, you need to use the full table names, as they are in the databases (searchable via Model.table_name)

Also, in your comment, you reference adding media_type: "line", which the below also includes:

Advertiser.includes(advertiser_account_groups: : v2_account_account_group)
          .where(media_type: "line", advertiser_account_groups: { v2_account_account_groups: { name: "something" } })

Probably the best way to structure this in your code is as a scope in your advertiser model, such as:

scope :by_v2_group_name, -> (name) { includes(advertiser_account_groups: :v2_account_account_group)
                               .where(media_type: "line", advertiser_account_groups: { v2_account_account_groups: { name: "something" } }) }

or

scope :by_v2_group_name, (lambda do |name| 
  includes(advertiser_account_groups: :v2_account_account_group)
  .where(media_type: "line", advertiser_account_groups: { v2_account_account_groups: { name: "something" } })
end)

That will then allow you to keep your code clean and call Advertiser.by_v2_group_name("something").

Let me know how you get on with that and we'll work on it as needed :)

Answer 2

This answer is relevant only if you don't need a model for the join table advertiser_account_gourps (if you don't have any extra attributes other then advertiser_id and account_gourp_id on that join table - you usually don't need it)

While @SRack's answer is using the has_many :through association, I think you can use a simple many-to-many association (has_and_belongs_to_many) because in your images i can see that the join table only have the ids of the associated tables (Unless you do need the id column -> and in that case, use @SRack's solution!)

class Advertiser < ActiveRecord::Base
  has_and_belongs_to_many :advertisers_account_groups

class AccountGroup < ActiveRecord::Base
  has_and_belongs_to_many :advertisers

then you can get them using:

advertiser = Advertiser.first
advertiser.account_groups #to get account groups of that advertiser

and vice versa:

account_group = AccountGroup.first
account_group.advertisers # to get advertisers of that account group

• @SRack's comment is correct! i changed it to HABTM association!

In that case you will need to create a migration to make the join table:

rails g migration CreateJoinTableAdvertiserAccountGroup advertiser account_group

This table will only include the associated advertiser_id and the account_group_id.

You don't need to create a model and handle stuff on that table, rails will fill that table for you.