I have an account model, that has many projects, and a project model, that has many requirements One account acts as a library account, and other accounts are the user accounts.
The requirement table has an external_id, which is a string id, which is used to correlate with an external database, and unique within the context of an account. But requirements in the library account and a user account, could share the same external_id (ie. "REQ-23"), so it is not an unique key on the Requirement table.
There is a RelatedRequirement table, that maps requirements that are related.
Below, I want to get the related requirements that belong to a user account, by making an account_id check in my has_many scope.
Database side:
account
id
project
id
account_id
requirement
id
project_id
external_id
related_requirements
external_id
related_external_id
Model side:
class RelatedRequirement < ActiveRecord::Base
belongs_to :requirement,,
foreign_key: :external_id,
class_name: "Requirement",
primary_key: :external_id
belongs_to :related_requirement,
foreign_key: :related_external_id,
class_name: "Requirement",
primary_key: :external_id
end
class Requirement < ActiveRecord::Base
belongs_to :project
has_one :account, through: :project
has_many :related_requirement_mappings,
foreign_key: :external_id,
class_name: "RelatedRequirement",
primary_key: :external_id
has_many :related_requirements,
# === this works ===
#-> { joins(:project).where(projects: {account_id: 2 }) },
# === this doesn't work ===
-> { joins(:project).where(projects: {account_id: project.account_id }) },
through: :related_requirement_mappings,
foreign_key: :related_external_id,
class_name: "Requirement",
primary_key: :external_id
My questions:
- Main question, I would like to be able to get the related_requirements within the same account using the has_many :related_requirements above, but I am unable to get to the associated account_id in my scope,
>req = Requirement.find(1)
>req.related_requirements
Requirement Load (81.3ms) SELECT "requirements".* FROM "requirements"
NameError (undefined local variable or method `project' for #<Requirement::ActiveRecord_Relation:0x00007fe3f0b1ac20>)
What I currently could do however, is:
class Requirement < ActiveRecord::Base
has_many :related_requirements,
#-> { joins(:project).where(prjects: {account_id: project.account_id }) },
through: :related_requirement_mappings,
foreign_key: :related_external_id,
class_name: "Requirement",
primary_key: :external_id
scope :for_account, -> (account) { joins(:project).where(projects: {account_id: account.id}) }
>req = Requirement.find(1)
>req.related_requirements.for_account(req.account)
This looks a bit silly, since it looks like I am calling an instance method on req, and I should be able to access req's account from inside related_requirements, without having to define this extra for_account scope.
- Since the external_id is not a unique field, the belongs_to requirement in the RelatedRequirement table actually could map to multiple requirements, but it just returns one. Ideally, I would want to add a scope condition (based on another database field cloned) that indicate it's from the library account. However, if I do that, then the above related_requirements query would break, since the scope condition would be included in the query, and no longer return the related_requirements of the user account, I am interested. I am unsure as to how come the condition I specify in the RelatedRequirement scope, is somehow applied in the scopes I am defining for Requirement when I am not explicitly invoking it.
class RelatedRequirement < ActiveRecord::Base
belongs_to :requirement,
-> { where cloned: nil },
foreign_key: :external_id,
class_name: "Requirement",
primary_key: :external_id
belongs_to :related_requirement,
-> { where cloned: nil },
foreign_key: :related_external_id,
class_name: "Requirement",
primary_key: :external_id
class Requirement < ActiveRecord::Base
belongs_to :project
has_one :account, through: :project
has_many :related_requirement_mappings,
foreign_key: :external_id,
class_name: "RelatedRequirement",
primary_key: :external_id
has_many :related_requirements,
# -> { joins(:project).where(projects: {account_id: project.account_id }) },
through: :related_requirement_mappings,
foreign_key: :related_external_id,
class_name: "Requirement",
primary_key: :external_id
scope :for_account, -> (account) { joins(:project).where(projects: {account_id: account.id}) }
irb(main):060:0> r.related_requirements.to_sql
=> "SELECT \"requirements\".* FROM \"requirements\" INNER JOIN \"related_requirements\" ON \"requirements\".\"external_id\" = \"related_requirements\".\"related_external_id\" WHERE \"related_requirements\".\"external_id\" = 'REQ-1' AND \"requirements\".\"cloned\" IS NULL"
irb(main):061:0> r.related_requirements.for_account(r.account)
=> #<ActiveRecord::AssociationRelation []>