The error
ERROR: missing FROM-clause entry for table "agency"
…should hint that somewhere in your query you have mistakenly used agency as a table name, without pluralizing it. But where exactly did you do that?
The only difference between working and non-working snippets of yours are these bits:
joins(:agency).
where(agency: {state: 'active'}).
…a-and both lines refer to agency. Welp , no easy path, need to examine both.
The joins part is responsible for generating an INNER JOIN part of the query, and if you look at the resulting SQL, it only uses agencies, which is the actual name of the table. This is because #joins accepts a symbol that denotes an association being joined — Rails traced it to a belongs_to association which, due to your adherence to naming conventions of Rails, points at the right model which knows its table name, allowing this to work.
The where part, however, is broken. This particular “sub-hash” condition form you used expects the hash key to be a table name and uses it in the query directly. Replace it with the actual name of the table and you should be good:
where(agencies: {state: 'active'})
The way of the #merge
There is another way to accomplish the same feat, and it involves, instead of #where, the #merge method, which merges the conditions of one relation into conditions of another:
merge( Agency.where(state: 'active') )
This does work even if the two are relations on different models, it’s handy for filtering joined records, which is exactly what you’re doing.
Additionally, it allows you to use some capabilities of the model class.
In particular, scopes:
# Inside Agency
scope :active, -> { where(state: 'active') }
# Somewhere else
merge(Agency.active)
Also, learning what the table name of the other model is. In #where you have to specify the table name in the query, breaking into the scope of the model’s database persistence, potentially from outside. With #merge you defer to the model, hopefully a single source of truth on that.