View fails to acknowledge the primary key of a table when using supabase.

[pcardosolei]

Bug report

Describe the bug

select() fails in certain situations to recognize to id of tables. The following situation is between a view and 2 tables to explain the situation.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Starting by adding the following script to the sql table.

DROP TABLE IF EXISTS "parents";
DROP TABLE IF EXISTS "childs";

CREATE TABLE "parents" (
    id uuid NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE "childs" (
    id uuid NOT NULL,
    name text NOT NULL,
    parent_id uuid NOT NULL
);

ALTER TABLE ONLY "childs"
    ADD CONSTRAINT "childs_parent_fk_id" FOREIGN KEY ("parent_id") REFERENCES "parents" ("id");

INSERT INTO parents (id, name) VALUES ('185aa629-43a0-4f3e-851b-7f685e9c1dc1', 'test');
INSERT INTO childs (id, name, parent_id) VALUES ('e2ef8d2a-b19c-4134-aa20-4ca1560df2b4', 'testing view', '185aa629-43a0-4f3e-851b-7f685e9c1dc1');

-- TO FAIL
CREATE OR REPLACE VIEW example_view AS
SELECT
    childs.id AS child_id,
    to_tsvector(
        'english', concat_ws(' ', parents.name, childs.name)) AS fts
FROM
    childs
    JOIN parents ON childs.parent_id = parents.id;

Query for the supabase-js:

export const searchText = async (text: string) => {
  const query = supabase.from("example_view").select("*,  child:childs(*)");
  const { data, error } = await query;
  return { data, error };
};

This will fail.

Expected behavior

I will show cases that will fail and cases that will not fail. Looking at the case to reproduce. The following select will fail to get the join of table and the view.

Looking at the SQL preview:

SELECT * FROM example_view JOIN childs on childs.id = example_view.child_id

Results in

  {
    "child_id": "e2ef8d2a-b19c-4134-aa20-4ca1560df2b4",
    "fts": "'test':1,2 'view':3",
    "id": "e2ef8d2a-b19c-4134-aa20-4ca1560df2b4",
    "name": "testing view",
    "parent_id": "185aa629-43a0-4f3e-851b-7f685e9c1dc1"
  }

Now. If i had the foreign_key in the view as the result of the select it will work.

CREATE OR REPLACE VIEW example_view AS
SELECT
    childs.id AS child_id,
    childs.parent_id AS parent_id, -- this line
    to_tsvector(
        'english', concat_ws(' ', parents.name, childs.name)) AS fts
FROM
    childs
    JOIN parents ON childs.parent_id = parents.id;

querying the parents table will work.

querying both will fail even thought he manages to get the parents.

Another example:

CREATE OR REPLACE VIEW example_view AS
SELECT
    childs.id AS child_id,
    parents.id AS parent_id,
    to_tsvector(
        'english', concat_ws(' ', parents.name, childs.name)) AS fts
FROM
    childs
    JOIN parents ON childs.parent_id = parents.id;

This will make the parents also fail.

but childs relationship starts working.

It is worth noting that I didn't manage to get successful results while querying both the tables when both id are in the view.

I tried different ways like parents!inner or parent!variable but with no success.

Screenshots

Added on the expected behavior.

System information

• OS: [e.g. macOS]
• Browser (if applies) [e.g. brave]
• Version of supabase-js: [e.g. 1.35.7]
• Version of Node.js: [e.g. 16.14.0]

Additional context

Add any other context about the problem here.

[steve-chavez]

Detecting views foreign keys is a best effort right now - they follow the rules on https://postgrest.org/en/stable/api.html#embedding-views.

CREATE OR REPLACE VIEW example_view AS
SELECT
    childs.id AS child_id,
    to_tsvector(
        'english', concat_ws(' ', parents.name, childs.name)) AS fts
FROM
    childs
JOIN parents ON childs.parent_id = parents.id;

The child_id from the view above comes from the childs table(childs.id) and on the next snippet

export const searchText = async (text: string) => { const query = supabase.from("example_view").select(", child:childs()"); const { data, error } = await query; return { data, error }; }; This will fail.

You do supabase.from("example_view").select(", child:childs()"), which try to embeds childs(where example_view comes from) with childs, since there's no FK relationship between childs and childs the detection will fail.

It is worth noting that I didn't manage to get successful results while querying both the tables when both id are in the view.

Try including childs.id, childs.parents_id and parents.parents_id in your view for this to work.

[steve-chavez]

Q: If you're doing the JOIN inside the view already, why do you need to do the join client-side? For this case it'd be simpler to have it all on the view.

[pcardosolei]

Detecting views foreign keys is a best effort right now - they follow the rules on https://postgrest.org/en/stable/api.html#embedding-views.

CREATE OR REPLACE VIEW example_view AS
SELECT
  childs.id AS child_id,
  to_tsvector(
      'english', concat_ws(' ', parents.name, childs.name)) AS fts
FROM
  childs
JOIN parents ON childs.parent_id = parents.id;

The child_id from the view above comes from the childs table(childs.id) and on the next snippet

export const searchText = async (text: string) => { const query = supabase.from("exampleview").select(", child:childs(_)"); const { data, error } = await query; return { data, error }; }; This will fail.

You do supabase.from("example_view").select(", child:childs()"), which try to embeds childs(where example_view comes from) with childs, since there's no FK relationship between childs and childs the detection will fail.

It is worth noting that I didn't manage to get successful results while querying both the tables when both id are in the view.

Try including childs.id, childs.parents_id and parents.parents_id in your view for this to work.

Makes sense. I was doing some extra tests + reading the documentation and I thought about it but was not 100% sure.

Thanks.

[pcardosolei]

Q: If you're doing the JOIN inside the view already, why do you need to do the join client-side? For this case it'd be simpler to have it all on the view.

I tried to do an approach to push the full text search out of the childs table because I tried to do some sort of combined FTS with combination of columns for the child table + parent table where most of the information came from the child table but a few important would also come from the parent table.

Need to think about a new way to do this then. Maybe doing a query to get the FTS results and then another to get the information from the desired table.

Thanks.