Rails PG::UndefinedTable: ERROR: missing FROM-clause entry for table

Question

I have models ``` class Offer < ActiveRecord::Base belongs_to :agency end class Agency < ActiveRecord::Base has_many :offers end ``` When I do such request - everything is OK ``` @offers = Offer.with_state(:confirmed). includes(:destination, :cruise_line, :ship). paginate(per_page: 10, page: params[:page]).decorate ``` But I want to select only offers that belong to `active` agencies (column `state` from `agencies` table), so I try to do like this: ``` @offers = Offer.with_state(:confirmed). includes(:destination, :cruise_line, :ship). joins(:agency). where(agency: {state: 'active'}). paginate(per_page: 10, page: params[:page]).decorate ``` Having done this I get error `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "agency"`. What is wrong with my code? The query gives me this error and sql: ``` PG::UndefinedTable: ERROR: missing FROM-clause entry for table "agency" LINE 1: ...id" WHERE ("offers"."state" IN ('confirmed')) AND "agency"."... ^ : SELECT "offers"."id" AS…

Accepted Answer

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.

Rails PG::UndefinedTable: ERROR: missing FROM-clause entry for table

Solution

Problem