Missing from clause entry for table ошибка

I’m trying to execute a join query for 4 tables on postgres.

Table names:

• scenarios_scenario
• payments_invoice
• payments_payment
• payments_action

(all those weird names are generated by django -)))

Relations:

• scenarios_scenario [has many] payments_actions
• payments_action [has one] payments_invoice
• payments_action [has one] payments_payment

Below one is a working query,

SELECT payments_invoice.*,
    (payments_payment.to_be_paid - payments_payment.paid) as remaining, \
    payments_action.identificator
FROM payments_invoice
JOIN payments_payment
  ON payments_invoice.action_id = payments_payment.action_id
  AND payments_payment.full_payment=2
JOIN payments_action
  ON payments_invoice.action_id = payments_action.id
  AND payments_action.identificator = %s

I just want to retrieve a related field from another table and wrote another query like

SELECT 
  scenarios_scenario.title, payments_invoice.*, \
  (payments_payment.to_be_paid - payments_payment.paid) as remaining, \
  payments_action.identificator, payments_action.scenario_id 
FROM payments_invoice 
JOIN scenarios_scenario 
  ON scenarios_scenario.id = payments_action.scenario_id
JOIN payments_payment 
  ON payments_invoice.action_id = payments_payment.action_id 
  AND payments_payment.full_payment=2 
JOIN payments_action 
  ON payments_invoice.action_id = payments_action.id 
  AND payments_action.identificator = 'EEE45667';

but facing with this error —

ERROR:  missing FROM-clause entry for table "payments_action"
LINE 2: ...IN  scenarios_scenario ON scenarios_scenario.id = payments_a...
                                                             ^

Looked across SO for similar questions like this (missing FROM-clause entry for table) but weren’t able to find a way. Any help would be appreciated.

I’m trying to execute a join query for 4 tables on postgres.

Table names:

• scenarios_scenario
• payments_invoice
• payments_payment
• payments_action

(all those weird names are generated by django -)))

Relations:

• scenarios_scenario [has many] payments_actions
• payments_action [has one] payments_invoice
• payments_action [has one] payments_payment

Below one is a working query,

SELECT payments_invoice.*,
    (payments_payment.to_be_paid - payments_payment.paid) as remaining, \
    payments_action.identificator
FROM payments_invoice
JOIN payments_payment
  ON payments_invoice.action_id = payments_payment.action_id
  AND payments_payment.full_payment=2
JOIN payments_action
  ON payments_invoice.action_id = payments_action.id
  AND payments_action.identificator = %s

I just want to retrieve a related field from another table and wrote another query like

SELECT 
  scenarios_scenario.title, payments_invoice.*, \
  (payments_payment.to_be_paid - payments_payment.paid) as remaining, \
  payments_action.identificator, payments_action.scenario_id 
FROM payments_invoice 
JOIN scenarios_scenario 
  ON scenarios_scenario.id = payments_action.scenario_id
JOIN payments_payment 
  ON payments_invoice.action_id = payments_payment.action_id 
  AND payments_payment.full_payment=2 
JOIN payments_action 
  ON payments_invoice.action_id = payments_action.id 
  AND payments_action.identificator = 'EEE45667';

but facing with this error —

ERROR:  missing FROM-clause entry for table "payments_action"
LINE 2: ...IN  scenarios_scenario ON scenarios_scenario.id = payments_a...
                                                             ^

Looked across SO for similar questions like this (missing FROM-clause entry for table) but weren’t able to find a way. Any help would be appreciated.

Missing from clause entry for table error statement doesn’t target only a query with a missing table name. You’ll find a variety of reasons leading you to the same error.

But there isn’t anything to worry about as long as you have this post opened on your screen because it will provide you with the much-needed guidance to fix the error instantly. Keep reading to discover the reasoning and solution parts for the error stated above.

Missing From Clause Entry for Table Error: Causes Described

The missing from clause entry for table error is caused due to skipping the table name in your query, using the where and join clauses interchangeably, or not using the duplicating option in Sequelize. Also, the lack of support to join tables for update queries in Knex can be problematic.

– You Haven’t Specified the Table Name for Your Operation

If you don’t specify the table name in the from clause while running a database query, you’ll encounter the missing from-clause entry for table Rails error. It is because the postgreSQL queries require the table name to complete your desired database operation.

For example, you want to select all of the columns from the employee table. Therefore, you are trying to run the select statement with the * asterisk sign that represents all columns. But you forget to specify the table name in the from clause. Here, the above error will pop up on your screen, complaining about the missing table name.

You can see the erroneous select statement below:

Note that even if you specify an incorrect table name, the same error will occur. So, you can say consider the following select statement problematic too because it targets employees table instead of the employee table.

– You Are Getting Confused Between the Where and Join Clauses

Not having clarity about the usage of where and join clauses can put you in trouble by directing the given error to your system. You might be trying to work with two tables simultaneously without using the join clause while relying on the where clause only.

Say that you want to select the designation column from table A based on a condition specified in the where clause. But the condition makes use of table A and table B. Now, if you use both of the tables in the where clause to create a condition without joining them, you’ll see the above error.

– The Duplicating Option in Sequelize

The duplicating option can be set to true or false. You can say that you can switch it on or off, but if you turn it on or off at the wrong time, the missing from-clause entry for table Sequelize error will land on your PC.

In case you don’t know, the duplicating option allows you to duplicate values while working with nested include queries. The nested include queries make it possible for you to join three tables where the third table is related to the second table.

– You Haven’t Used the references() Method With the Includes

The missing from-clause entry for table active record might occur when you don’t use the references() method with the includes. This way, the table specified in includes doesn’t join the string specified in the where clause leading to a failed query execution.

You must know that the references() method tells that the specified table must not be loaded separately. Instead, it should be joined in the SQL string for flawless results. Here you go with an erroneous code:

User.includes(:students).where(“students.name = ‘John’”)

– Knex Won’t Allow You To Join Data for Update Queries

Knex doesn’t allow you to join tables to execute the update queries. Therefore, if you attempt to join data for update queries, the missing from-clause entry for table Knex will start flashing on your screen. So, you can mark the inefficiency of Knex to be the culprit.

However, the lack of support for the stated operation in Knex doesn’t make it a bad query builder because it has its good sides too.

Missing From Clause Entry for Table Error: How To Fix?

You can fix the missing from clause entry for table error by mentioning the correct table name in the from or join clause or using the where and join clauses wisely. Moreover, setting the duplicating option in Sequelize or executing individual table update queries or subqueries in Knex will help.

– Mention the Correct Table Name in the From Clause

You should never miss specifying the table name in the from clause to fix the missing from-clause entry for table GORM error. Plus, it would be best to check the spelling of the table name to ensure that you type it correctly in the from clause.

So, the simplest of the solutions to resolve the missing from-clause entry for table typeORM error is to stay right with your table name. You can use the following query to select all of the columns from the employee table:

– Specify the Table Name in the Join Clause At least

If you are selecting multiple columns from two different tables, then you should specify one table name in the from clause while stating another table name in the join clause. This way, you’ll satisfy the query expectations and fix the error occurring on your PC.

Say that you want to select the grade column from the student table and the subject column from the teacher table. In such a situation, your query must look like the one given below to achieve your desired results:

SELECT student.grade, teacher.subject from student INNER JOIN teacher on student.s_ID = teacher.s_ID

– Use the Join and Where Clauses Wisely

Understanding the purpose and usage of the join and where clauses can help you use the said clauses wisely and lower the chances of the given error. You should know that the where clause is used to specify a condition, while the join clause is used to join two tables.

Therefore, you can’t use them interchangeably. Moreover, sometimes, you might need to use the WhereHas clause for writing and executing complex queries in Laravel. The said clause will make things easier for you by applying the where condition on your has queries and help you remove the missing from-clause entry for table Laravel error.

– Turn On or Off the Duplicating Option in Sequelize

It is recommended to turn on the duplicating option in Sequelize for all the child include statements to get rid of the error stated in the title. All that you’ll have to do is to the set duplicating:true for each nested include statement.

Also, you should note that sometimes, duplicating:false sorts out the problem too. So, you might like to check which option works in your favor.

– Leverage the references() Method With Includes

It would be best to leverage the references() method with includes to ensure that the table name specified in the includes joins with the SQL string to create a sensible query. Consequently, the error will quickly disappear from your screen.

Targeting the example shared earlier, all that you’ll have to do is to append the references() method to your code for an error-free execution. Here is the corrected code:

User.includes(:students).where(“students.name = ‘John’”).references(:students)

– Execute Separate Update Queries or Go for the Subquery Concept

If you are using Knex, then you should skip the joining data or tables part and go for separate update query executions to push away the error. Also, you might like to create a subquery inside your main update query to ensure that Knex is able to execute it.

In short, staying away from joining tables to update your data in Knex is considered a good practice and will be super helpful in avoiding the given error.

Conclusion

As per this post, you need to have a clear understanding of the different SQL clauses to write complete and valid queries. Moreover, you might find working on PGAdmin or related tools easier to use than the ORMs, and it will consequently help you to avoid the missing from-clause entry for table SQLAlchemy error. Lastly, go through the following listicle to wrap up the guide.

• You should enter the table name in the from or join clause to ensure the completeness of your query.
• Using the references() method with the includes will allow joining the table name in the where clause string to resolve the error.
• Never join tables for update queries while using Knex to maintain distance from the said error.
• Switch on the duplicating option for the child include queries in Sequelize to get the error fixed.
• Never use the where and join clauses interchangeably.

Please feel free to use this article to figure out the problems in your queries and code and have an error-free database management experience.

Problem Description:

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 t0_r0, "offers"."name" AS t0_r1, "offers"."destination_id" AS t0_r2, "offers"."cruise_line_id" AS t0_r3, "offers"."ship_id" AS t0_r4, "offers"."departure_date" AS t0_r5, "offers"."departure_port_id" AS t0_r6, "offers"."arrival_date" AS t0_r7, "offers"."arrival_port_id" AS t0_r8, "offers"."flight_price" AS t0_r9, "offers"."bonus" AS t0_r10, "offers"."itinerary" AS t0_r11, "offers"."board_language_id" AS t0_r12, "offers"."agency_landing_page" AS t0_r13, "offers"."benefits" AS t0_r14, "offers"."inner_price" AS t0_r15, "offers"."inner_price_normal" AS t0_r16, "offers"."outer_price" AS t0_r17, "offers"."outer_price_normal" AS t0_r18, "offers"."balcony_price" AS t0_r19, "offers"."balcony_price_normal" AS t0_r20, "offers"."suite_price" AS t0_r21, "offers"."suite_price_normal" AS t0_r22, "offers"."lucky_price" AS t0_r23, "offers"."lucky_price_normal" AS t0_r24, "offers"."valid_from" AS t0_r25, "offers"."valid_till" AS t0_r26, "offers"."created_at" AS t0_r27, "offers"."updated_at" AS t0_r28, "offers"."description" AS t0_r29, "offers"."agency_id" AS t0_r30, "offers"."state" AS t0_r31, "destinations"."id" AS t1_r0, "destinations"."name" AS t1_r1, "destinations"."created_at" AS t1_r2, "destinations"."updated_at" AS t1_r3, "cruise_lines"."id" AS t2_r0, "cruise_lines"."name" AS t2_r1, "cruise_lines"."created_at" AS t2_r2, "cruise_lines"."updated_at" AS t2_r3, "ships"."id" AS t3_r0, "ships"."name" AS t3_r1, "ships"."picture" AS t3_r2, "ships"."cruise_line_id" AS t3_r3, "ships"."created_at" AS t3_r4, "ships"."updated_at" AS t3_r5 
FROM "offers" 
INNER JOIN "agencies" ON "agencies"."id" = "offers"."agency_id" 
LEFT OUTER JOIN "destinations" ON "destinations"."id" = "offers"."destination_id" 
LEFT OUTER JOIN "cruise_lines" ON "cruise_lines"."id" = "offers"."cruise_line_id" 
LEFT OUTER JOIN "ships" ON "ships"."id" = "offers"."ship_id" 
WHERE ("offers"."state" IN ('confirmed')) AND "agency"."state" = 'active' 
LIMIT 10 OFFSET 0

Solution – 1

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.