Could not find a relationship in the schema cache
Here's the query I want to run
SELECT project_members.user_id, users_lookup.email, users_lookup.first_name, users_lookup.last_name
FROM project_members
INNER JOIN users_lookup
ON project_members.user_id = users_lookup.user_id
WHERE project_members.project_id = 'abcdefgh';
This query runs perfectly in the SQL editor in supabase. Now i want to run the same query through javascript. I'm following the documentation to the write this query in javascript
const { data, error } = await supabase
.from("project_members")
.select(
user_id,users_lookup!inner(first_name, last_name, email))
.eq("project_id",
project_id
).eq("users_lookup.user_id", "project_members.user_id");
This join needs to select the data from the users_lookup table where the user_id from the project_members table matches with the user_id in the users_lookup table and also the project_id should match the id in project_id variable so essentially I'm selecting members from the table who's project_id matches the one given and then for those users I'm using the user_id to get the respective data from the users_lookup table
When I try to run this I'm getting this error
Could not find a relationship between 'project_members' and 'users_lookup' in the schema cache
6 Replies
You have to have a foreign key between the tables for the REST API to do joins.
.eq will not work as you show as it needs one column and a constant.
So I need to add the user_id from the lookup table as foreign key in the user_id in project_members. And do I need to add the second eq or after adding the foreign key would the first one be enough?
The REST API figures out the JOIN based on an fk in either table pointing to the other or even thru a 3rd party table with fks to each table.
Thank you.
Also quick question. This query works through the SQL editor even though the fk is not setup, but can't seem to replicate it through the JS library. Is it possible to run that query using the JS library of supabase, or would I need a ORM for this type of query?
No. You would need to use a view or rpc call to a postgres function and return a table/setof. And yes you can use an ORM with the direct ports also.
Thank you @garyaustin creating the view worked and it simplified the whole process. I think this will work a long way before having to rely on an ORM