Rails Pg::Undefinedtable: Error: Missing From-Clause Entry for Table

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

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 Includes Error : UndefinedTable: ERROR: missing FROM-clause entry for table

You need to join those tables if you want to query them, includes is not enough:

@students = Student.joins(register: :schedule)

or add a references call:

@students = Student.includes(register: :schedule).references(register: :schedule)

includes will reduce the number of database queries to access the included tables but it won't JOIN the tables.

PG::UndefinedTable: ERROR: missing FROM-clause entry for table when using joins and where

Hmm it looks like you're trying to include current_orders and include order. Are these the same tables with different conditions? This might be confuse active record. Also, I'm pretty sure it's wise to include the references method when referencing a joined table. Perhaps, try something like this:

active_couriers = Courier.includes(:orders)
  .available_courier_status
  .where(:service_region_id => @service_region.id)
  .where("orders.created_at >= ?", Time.zone.now.beginning_of_day)
  .references(:orders)

PG::UndefinedTable: ERROR: missing FROM-clause — Searching with two models Rails

The where method expects to receive the exact table name (see full example here: How to query a model based on attribute of another model which belongs to the first model?):

@books = Book.where({title: params[:book][:title]})
@books = @books.joins(:user).where(users: {university: params[:users][:university]}).uniq
#                     ^ relation name  ^ exact name of the table

If, for some reason, the name of the table storing the User records was named utilisateurs, then the where usage would be:

@books = Book.joins(:user).where(utilisateurs: { name: 'Bob' })

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

Try changing team.name to teams.name in (mind s)

TeamSeason.where(season: @season).includes(:team).order("team.name")

---

Related Topics