"неверная ссылка на запись FROM-предложения для таблицы" в запросе Postgres

Question

У меня есть следующий запрос:

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"

И это вызывает следующую ошибку:

недопустимая ссылка на запись предложения FROM для таблицы "assign_intakes"

Ошибка начала происходить после того, как я добавил:

LATERAL jsonb_object_keys(data #> '{products}') keys

а также

(count(keys))::numeric as \"numProducts\"

потому что мне нужно было рассчитать количество продуктов.

Как я могу избежать этой ошибки?

Answer 1

Объясните ошибку

Непосредственной причиной появления сообщения об ошибке является то, что любое явное JOIN связывает сильнее, чем запятая (,) который в противном случае эквивалентен CROSS JOIN, но ( согласно документации):

Примечание: эта последняя эквивалентность не выполняется точно, когда появляется более двух таблиц, потому что JOIN связывает более плотно, чем запятая. Например FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition это не то же самое, что FROM T1, T2 INNER JOIN T3 ON condition поскольку condition может ссылаться 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