-
-
Notifications
You must be signed in to change notification settings - Fork 1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Many-To-Many join fails when a view in another exposed schema has the same name as a table in the target schema #3614
Comments
To Reproduce in PostgRESTBoth schemas need to be in
Then, the request would be: curl 'localhost:3000/one?select=name,list:two(name)' Issue
I would expect so too. I had my doubts since PostgREST allows embedding through views and thought that maybe this is detecting {
"code": "42P01",
"details": null,
"hint": "There is an entry for table \"one_two\", but it cannot be referenced from this part of the query.",
"message": "invalid reference to FROM-clause entry for table \"one_two\""
} So, there's definitely something wrong with a) the embedding detection or b) with building the query. I believe that it's both so I'm tagging this one as a bug for now. Extra info: Part of the SQL querySELECT "api"."one"."name",
COALESCE("one_list_1"."one_list_1", '[]') AS "list"
FROM "api"."one"
LEFT JOIN LATERAL
(SELECT json_agg("one_list_1")::jsonb AS "one_list_1"
FROM
(SELECT "api"."two"."name"
FROM "api"."two",
"public"."one_two"
WHERE "api"."one_two"."two_id" = "api"."two"."id"
AND "api"."one_two"."one_id" = "api"."one"."id")
AS "one_list_1")
AS "one_list_1" ON TRUE) WorkaroundFor now, you can disambiguate the many-to-many relationships using spread embeds. So, the request would be: curl 'localhost:3000/one?select=name,list:one_two(...two(name))' Or, I'm assuming it's like this in const { data, error } = await s.from("one").select("name,list:one_two(...two(name))"); |
Thanks for the quick response! Our use case is that the api schema will serve as our public api, and changing all queries in our app that target the public schema is not an option. Let me know if I can support here further. |
Why is You should only add the schema to be exposed as the api there. |
Agreed. |
because we are using the |
Ah, interesting. You could also spin up two postgrest instances, one for each. This way you would be able to avoid dealing with all the Profile headers etc., too. But ultimately, this is a bug, yes. |
Environment
Description of issue
A Postgrest many-to-many query fails if a table in the "primary" schema has the same name as a view in another schema also exposed by postgrest.
My expectation is that postgrest prefers the table in the primary schema. Note that explicitly setting the db schema on the supabase client also does not change anything.
Reproduction: https://github.com/psteinroe/postgrest-repro
Schema:
The query (using supabase-js):
The text was updated successfully, but these errors were encountered: