Rails: List of a nested items through a many-to-many relation model

Question

I am new to rails and there's something I don't fully understand.

I have 4 models as follows:

class Unit
  belongs_to :compound
  belongs_to :unit_type
end

class UnitType
  has_many :units
  has_many :unit_type_compounds
  has_many :compounds, through: :unit_type_compounds
end

class Compound
  has_many :units
  has_many :unit_type_compounds
  has_many :unit_types, through: :unit_type_compounds
end

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound
end

I want to get all units [with active status] through a unit_type_compound

I've tried the following but when I inspect the query I find it's wrong

first:

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound

  has_many :units, through: :unit_type
end

the query [for a single item UnitTypeCompound.all.first.units] was something like:

SELECT `units`.*
FROM `units`
  INNER JOIN `unit_types` ON `units`.`unit_type_id` = `unit_types`.`id`
WHERE `unit_types`.`id` = ?
  /* loading for inspect */
LIMIT 11

second:

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound
  has_many :units, through: :unit_type, source: :unit_type_compounds

  has_many :vacant_units, ->{ where(status: :vacant) }, class_name: Unit.to_s,
           through: :unit_type, source: :unit_type_compounds
end

the query [for a single item UnitTypeCompound.all.first.units] was something like:

SELECT `units`.*
FROM `units`
  /* Note: the following condition is units.id, not compounds.id */
  INNER JOIN `unit_type_compounds` ON `units`.`id` = `unit_type_compounds`.`compound_id`
  INNER JOIN `unit_types` ON `unit_type_compounds`.`unit_type_id` = `unit_types`.`id`
WHERE `unit_types`.`id` = ?
  /* loading for inspect */
LIMIT 11

and query [for a single item UnitTypeCompound.all.first.vacant_units] was something like:

SELECT `units`.*
FROM `units`
  /* Note: the same condition */
  INNER JOIN `unit_type_compounds` ON `units`.`id` = `unit_type_compounds`.`compound_id`
  INNER JOIN `unit_types` ON `unit_type_compounds`.`unit_type_id` = `unit_types`.`id`
WHERE `unit_types`.`id` = ?
  AND `units`.`status` = 0
  /* loading for inspect */
LIMIT 11

third:

It worked using the method

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound

  def vacant_units
    Unit.where(status: :vacant)
        .where(compound: compound)
        .where(unit_type: unit_type)
  end
end

and query [for a single item UnitTypeCompound.all.first.vacant_units] was something like:

SELECT `units`.*
FROM `units`
WHERE `units`.`sellable` = FALSE
    AND `units`.`compound_id` = ?
    AND `units`.`unit_type_id` = ?
    AND `units`.`status` = 0
    /* loading for inspect */
LIMIT 11

but, I wonder if there's a way through has_many or something?

Answer 1

Your scenario is not suitable for has_many for a few reasons. You are in a situation where UnitTypeCompound has many units through two different associations and you want an intersection of units from these two associations.

First reason this should not work with has_many is that using natural language you would expect that if UnitTypeCompound has many units it would be a union of those two associations, not an intersection.

Second reason is that has_many should be reversible. If you call unit.unit_type_compounds would you expect it to be unit.unit_type.unit_type_compounds, unit.compound.unit_type_compounds, union of those two or their intersection?

Third is that you should be able to call collection<< method on the association. If you called unit_type_compound.units << Unit.last should it create that association through UnitType or Compound?

Your scenario has a lot of abibiguity that cannot be expressed by a simple has_many :units, through: therefore if the vacant_units method works for you, I would stick with it.

If you want to try to do it with an association, my best guess is that it should look like this:

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound

  has_many :vacant_units, ->(unit_type_compound){ where(compound_id: unit_type_compound.compound_id, status: :vacant) },
           through: :unit_type, source: :units 
end