I have the following query:
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"
And it is causing the following error:
invalid reference to FROM-clause entry for table "appointment_intakes"
The error started happening after I added:
LATERAL jsonb_object_keys(data #> '{products}') keys
and
(count(keys))::numeric as \"numProducts\"
because I needed to calculate the number of products.
How can I avoid this error from happening?
The immediate cause for the error message is that any explicit JOIN
binds stronger than a comma (,
) which is otherwise equivalent to a CROSS JOIN
, but (per documentation):
Note: This latter equivalence does not hold exactly when more than two tables appear, because
JOIN
binds more tightly than comma. For exampleFROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition
is not the same asFROM T1, T2 INNER JOIN T3 ON condition
because thecondition
can referenceT1
in the first case but not the second.
Bold emphasis mine.
This is the cause of your error. You could fix it:
FROM appointment_intakes
CROSS JOIN LATERAL jsonb_object_keys(data #> '{products}') keys
INNER JOIN appointment_intake_users ON ...
But that was not the only problem. Keep reading.
One might argue that Postgres should see that LATERAL
only makes sense in connection with the table to the left. But that's not so.
I added table aliases, and table-qualified all column names as suspected. While being at it, I simplified the JSON references and trimmed some noise. This query is still incorrect:
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
Based on the above and some more assumptions, the solution could be to do the count in a subquery:
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;
Since you only need the count, I converted your LATERAL
join into a correlated subquery, thereby avoiding the various problems arising from multiple 1:n joins combined. More:
You need to escape identifiers properly, use a prepared statement and pass values as values. Don't concatenate values into the query string. That's an invitation for random errors or SQL injection attacks. Recent example for PHP: