Rails: 通过多对多关系模型列出嵌套项目的列表

huangapple go评论58阅读模式
英文:

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

问题

以下是您要翻译的内容:

"我是Rails的新手,有一些东西我不太明白。

我有4个模型,如下所示:

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

我想通过unit_type_compound获取所有带有活动状态的units

我尝试了以下方法,但当我检查查询时,发现它是错误的。

第一种:

class UnitTypeCompound
  belongs_to :unit_type
  belongs_to :compound

  has_many :units, through: :unit_type
end

查询(针对单个项目UnitTypeCompound.all.first.units)如下所示:

SELECT `units`.*
FROM `units`
  INNER JOIN `unit_types` ON `units`.`unit_type_id` = `unit_types`.`id`
WHERE `unit_types`.`id` = ?
  /* 用于检查加载 */
LIMIT 11

第二种:

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

查询(针对单个项目UnitTypeCompound.all.first.units)如下所示:

SELECT `units`.*
FROM `units`
  /* 注意:以下条件是units.id,而不是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` = ?
  /* 用于检查加载 */
LIMIT 11

以及查询(针对单个项目UnitTypeCompound.all.first.vacant_units)如下所示:

SELECT `units`.*
FROM `units`
  /* 注意:相同的条件 */
  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
  /* 用于检查加载 */
LIMIT 11

第三种:

使用以下方法可以解决问题:

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

查询(针对单个项目UnitTypeCompound.all.first.vacant_units)如下所示:

SELECT `units`.*
FROM `units`
WHERE `units`.`sellable` = FALSE
    AND `units`.`compound_id` = ?
    AND `units`.`unit_type_id` = ?
    AND `units`.`status` = 0
    /* 用于检查加载 */
LIMIT 11

不过,我想知道是否有一种通过has_many或其他方式来实现的方法?"

英文:

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?

答案1

得分: 1

你的情景不适合使用has_many,有几个原因。您处于一种情况,UnitTypeCompound通过两个不同的关联关系具有许多units,并且您希望从这两个关联关系中获取units的交集。

第一个原因,这不应该与has_many一起使用,因为使用自然语言,您会期望如果UnitTypeCompound有许多单位,它将是这两个关联的并集,而不是交集。

第二个原因是,has_many应该是可逆的。如果您调用unit.unit_type_compounds,您会期望它是unit.unit_type.unit_type_compoundsunit.compound.unit_type_compounds,这两者的并集还是它们的交集?

第三个原因是,您应该能够在关联上调用collection<<方法。如果您调用unit_type_compound.units << Unit.last,它应该通过UnitType还是Compound创建该关联?

您的情景存在很多歧义,不能简单地通过has_many :units, through:来表达,因此如果vacant_units方法适用于您,我建议您坚持使用它。

如果您想尝试使用关联来实现它,我的最佳猜测是应该像这样:

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
英文:

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&lt;&lt; method on the association. If you called unit_type_compound.units &lt;&lt; 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, -&gt;(unit_type_compound){ where(compound_id: unit_type_compound.compound_id, status: :vacant) },
           through: :unit_type, source: :units 
end

huangapple
  • 本文由 发表于 2023年3月7日 15:28:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/75659057.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定