has_many scope with a condition check on a nested association

Viewed 239

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:

  1. 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.

  1. 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 []>

1 Answers

I found the answer to my own question, posting for future refernce.

If I define the scope in my has_many relationship with an argument, that argument would be the self object, but I don't need to call the has_many with that argument. It would be implicitly passed in.

has_many :related_requirements,
    (requirement) -> { joins(:project).where(projects: {account_id: requirement.account_id }) },
    through: :related_requirement_mappings, 
    foreign_key: :related_external_id, 
    class_name: "Requirement",
    primary_key: :external_id

Then I am able to get the related requirements scoped by a condition of belonging to the account (which is a nested association):

irb(main):028:0> r.related_requirements.to_sql
  Account Load (6.0ms)  SELECT  "accounts".* FROM "accounts" INNER JOIN "projects" ON "accounts"."id" = "projects"."account_id" WHERE "projects"."id" = $1 LIMIT 1  [["id", 9]]
=> "SELECT \"requirements\".* FROM \"requirements\" INNER JOIN \"projects\" ON \"projects\".\"id\" = \"requirements\".\"project_id\" INNER JOIN \"related_requirements\" ON \"requirements\".\"external_id\" = \"related_requirements\".\"related_external_id\" WHERE \"related_requirements\".\"external_id\" = 'REQ-1' AND \"projects\".\"account_id\" = 2"
Related