当前位置:  开发笔记 > 数据库 > 正文

Postgres查询中的“对表的FROM子句条目的无效引用”

如何解决《Postgres查询中的“对表的FROM子句条目的无效引用”》经验,为你挑选了1个好方法。

我有以下查询:

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\"

因为我需要计算产品数量。

如何避免发生此错误?



1> Erwin Brands..:

说明错误

该错误消息的直接原因是,任何显式JOIN绑定都比逗号(,)更强,而逗号()等效于CROSS JOIN,但是(根据文档):

注意:当出现两个以上的表时,后者的等价关系并不完全成立,因为JOIN绑定比逗号更紧密。例如 FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition,这与 FROM T1, T2 INNER JOIN T3 ON condition 因为conditioncan引用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时遇到的问题

推荐阅读
个性2402852463
这个屌丝很懒,什么也没留下!
DevBox开发工具箱 | 专业的在线开发工具网站    京公网安备 11010802040832号  |  京ICP备19059560号-6
Copyright © 1998 - 2020 DevBox.CN. All Rights Reserved devBox.cn 开发工具箱 版权所有