SQL Postgres：缺少表的 FROM 子句条目

Postgres: missing FROM-clause entry for table

提问by marmeladze

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

我正在尝试对 postgres 上的 4 个表执行连接查询。

Table names:

表名：

• scenarios_scenario
• payments_invoice
• payments_payment
• payments_action

• scenarios_scenario
• payments_invoice
• payments_payment
• payments_action

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

（所有那些奇怪的名字都是由 django 生成的 -）））

Relations:

关系：

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

• scenarios_scenario[有很多] payments_actions
• payments_action[有一个] payments_invoice
• payments_action[有一个] 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.

在 SO 中寻找类似的问题（缺少表的 FROM 子句条目），但无法找到方法。任何帮助，将不胜感激。

回答by Laposhasú Acsa

In your first join 'payments_action' is not a known relation. Reorder your joins in a way that a new join only uses already 'defined' relations.

在您第一次加入时，'payments_action' 不是已知的关系。以新连接仅使用已定义的关系的方式重新排序您的连接。

Here is a fiddle, demonstrating the issue:

这是一个小提琴，证明了这个问题：

http://sqlfiddle.com/#!17/ed147/5

http://sqlfiddle.com/#!17/ed147/5

回答by Bryan Schwimmer

Change the code so that you join each table before calling a column from it in another join. The postgres query planner reads the joins sequentially so that in your code table scenarios_scenariois being joined to table payments_invoiceand is looking for a match with payments_action, but the query planner doesn't know what payments_actionis yet. The new code should be:

更改代码，以便在从另一个联接中调用其中的列之前联接每个表。postgres 查询计划器按顺序读取连接，以便在您的代码表scenarios_scenario中连接到表payments_invoice并寻找与 匹配payments_action，但查询计划器还不知道是什么payments_action。新代码应该是：

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 payments_action 
ON (
    payments_invoice.action_id = payments_action.id 
    AND payments_action.identificator = 'EEE45667'    
)
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 
);

回答by stubs

You are joining to table [scenarios_scenario] using a field from [payments_action].

您正在使用 [payments_action] 中的字段加入表 [scenarios_scenario]。

The joins must be in sequence, i.e. you cannot reference fields from a table with the ON statement unless their tables precede the statement.

连接必须按顺序进行，即您不能使用 ON 语句引用表中的字段，除非它们的表在语句之前。

Hope that helps

希望有帮助