CODEWITHSUNDEEP
ruby-on-railsjoinactiverecordrails-activerecord
Suganya Selvarajan
Suganya Selvarajan

Reputation: 1092

Rails ActiveRecord joins with multiple tables

I have a Category Model which belongs to Offer Model through a third Model OfferCategory. I also have a similar association for PlumCake Model which is associated to Category model through PlumCakeCategory Model.

Category: 

has_many :offer_categories, dependent: :destroy, inverse_of: :category
has_many :offers, through: :offer_categories, source: :offer
has_many :plum_cake_categories, dependent: :destroy, inverse_of: :category
has_many :plum_cakes, through: :plum_cake_categories, source: :plum_cake

Offer:

has_many :offer_categories, dependent: :destroy, inverse_of: :offer
has_many :categories, through: :offer_categories, source: :category, dependent: :destroy

OfferCategory:

belongs_to :offer
belongs_to :category

PlumCake:

 has_many :plum_cake_categories, dependent: :destroy, inverse_of: :plum_cake
has_many :categories, through: :plum_cake_categories, source: :category, dependent: :destroy

PlumCakeCategory:

belongs_to :plum_cake
belongs_to :category

and a similar association for category/plumcakes as well.

Now I want to get all categoires that the selected offers and plumcakes has. The following query gives me the list of categories that the eligible_offer_ids offers has. 

Category.joins(:offer_categories).where(offer_categories: { offer_id: eligible_offer_ids })

I can fire a similar query for plum_cake and get uniq categoires of these two queries. 

cat1 = Category.joins(:offer_categories).where(offer_categories: { offer_id: eligible_offer_ids })
cat2 = Category.joins(:plum_cake_categories).where(plum_cake_categories: { plum_cake_id: eligible_plum_cake_ids })
(cat1 + cat2).uniq

But Is there a way I get the same result((cat1 + cat2).uniq) in a single query?

Upvotes: 3

Views: 1156

Answers (2)

Moeen
Moeen

Reputation: 412

if you don't to want change your structure:

Category.left_outer_joins(:offer_categories, :plum_cake_categories).where(offer_categories: { offer_id: eligible_offer_ids }).or(Category.left_outer_joins(:offer_categories, :plum_cake_categories).where(plum_cake_categories: { plum_cake_id: eligible_plum_cake_ids })).uniq

Upvotes: 1

Moeen
Moeen

Reputation: 412

I think if you use rails STI(single table inheritance) you can easily implement your functionality.

if your structure be somthing like following:

class Category
  has_many :offer_categories, dependent: :destroy, inverse_of: :category
  has_many :offers, through: :offer_categories, source: :offer
  has_many :plum_cake_categories, dependent: :destroy, inverse_of: :category
  has_many :plum_cakes, through: :plum_cake_categories, source: :plum_cake
  has_many :sub_categories
end

class SubCategory
  belongs_to :offer
  belongs_to :plum_cake
  belongs_to :category
end

class OfferCategory < SubCategory
  validate_presence_of :offer_id
end

class PlumCakeCategory < SubCategory
  validate_presence_of :plum_cake_id
end

your query will be:

Category.joins(:sub_categories).where(sub_categories: { offer_id: eligible_offer_ids }).or(Category.joins(:sub_categories).where(sub_categories: { plum_cake_id: eligible_plum_cake_ids }))

STI documentation

Upvotes: 1

Related Questions