我有以下查询:
query = "SELECT data #>> '{id}' AS id, data #>> '{name}' AS name, data #>> '{curator}' AS curator, data #> '{$isValid}' AS \"$isValid\", data #> '{customer}' AS customer, data #> '{$createdTS}' AS \"$createdTS\", data #> '{$updatedTS}' AS \"$updatedTS\", data #> '{$isComplete}' AS \"$isComplete\", (count(keys))::numeric as \"numProducts\", created_at FROM appointment_intakes, LATERAL jsonb_object_keys(data #> '{products}') keys INNER JOIN appointment_intake_users ON appointment_intake_users.appointment_intake_id = appointment_intakes.id #{where_clause} GROUP BY id"
并导致以下错误:
对表“ appointment_intakes”的FROM子句条目的无效引用
添加后,该错误开始发生:
LATERAL jsonb_object_keys(data #> '{products}') keys
和
(count(keys))::numeric as \"numProducts\"
因为我需要计算产品数量。
如何避免发生此错误?
该错误消息的直接原因是,任何显式JOIN
绑定都比逗号(,
)更强,而逗号()等效于CROSS JOIN
,但是(根据文档):
注意:当出现两个以上的表时,后者的等价关系并不完全成立,因为
JOIN
绑定比逗号更紧密。例如FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition
,这与FROM T1, T2 INNER JOIN T3 ON condition
因为condition
can引用T1
在第一种情况而不是第二种情况不同。
大胆强调在最后的地雷。这正是您出错的原因。您可以修复它:
FROM appointment_intakes CROSS JOIN LATERAL jsonb_object_keys(data #> '{products}') keys INNER JOIN appointment_intake_users ON ...
但这不是查询中的唯一问题。见下文。
有人可能会争辩说Postgres应该看到这LATERAL
仅与左侧的表格有关。但是,要迅速变得聪明起来会给您带来麻烦。最好对此严格。
我添加了表别名,并怀疑是否有表限定的所有列名。在此期间,我简化了JSON引用并减少了一些噪音。查询仍然不正确:
"SELECT i.data ->> 'id' AS id, i.data ->> 'name' AS name, i.data ->> 'curator' AS curator, i.data -> '$isValid' AS \"$isValid\", i.data -> 'customer' AS customer, i.data -> '$createdTS' AS \"$createdTS\", i.data -> '$updatedTS' AS \"$updatedTS\", i.data -> '$isComplete' AS \"$isComplete\", count(k.keys)::numeric AS \"numProducts\", u.created_at FROM appointment_intakes i , jsonb_object_keys(i.data -> 'products') AS k(keys) JOIN appointment_intake_users u ON u.appointment_intake_id = i.id #{where_clause} GROUP BY i.id"
如果这是正确的,并且基于其他一些假设,则解决方案可能是在子查询中进行计数,例如:
基于以上假设:
SELECT i.data ->> 'id' AS id, i.data ->> 'name' AS name, i.data ->> 'curator' AS curator, i.data -> '$isValid' AS "$isValid", i.data -> 'customer' AS customer, i.data -> '$createdTS' AS "$createdTS", i.data -> '$updatedTS' AS "$updatedTS", i.data -> '$isComplete' AS "$isComplete", (SELECT count(*)::numeric FROM jsonb_object_keys(i.data -> 'products')) AS "numProducts", min(u.created_at) AS created_at FROM appointment_intakes i JOIN appointment_intake_users u ON u.appointment_intake_id = i.id -- #{where_clause} GROUP BY i.id
由于只需要计数,因此我将您的LATERAL
联接转换为相关的子查询,从而避免了由于多个1:n联接组合而产生的各种问题。更多:
LATERAL和PostgreSQL中的子查询有什么区别?
两个SQL LEFT JOINS产生不正确的结果
您需要正确地转义标识符,使用准备好的语句并将值作为值传递。不要将值连接到查询字符串中。这是随机错误或SQL注入攻击的诱因。
这是最近的PHP示例:
我尝试实施UPSERT时遇到的问题