Hey guys! @Broberto@Joyce Elementary questions here… What is the best way to get another table with a foreign key from another (AKA Joins) form supabase?
Example, a table called “users” has a user ID integer. That user id integer is a foregin key in a table called “accounts”, and I want to get the account ID based upon that user ID integer.
I know its simple, but just need to be pointed in the best way to do this within weweb. Currently I am changing variables frequently and I am noticing its not the most ideal method. I assume you will recommend an array formula, and if thats the case do you have an example that I can go by and replicate that for all the joins id like to make?
CREATE VIEW users_foreign_key_account -- Your View Name
AS
SELECT * -- Your desired columns from each joined table
-- Here begins the JOIN tables part, so you're selecting FROM joined table ..
FROM users -- This is the first table, in your case would be users
JOIN accounts ON users.id = accounts.account_id -- Now you join it with your table on your foreign key (I suppose it's accounts.account_id)
Then you just call the view from Supabase plugin (don’t forget to refresh the tables)
You could do this via Advanced in the Supabase plugin by doing something like this
*,accounts:id(*)
like this, but it’s error prone, and it will most certainly throw you an error, because id is too ambiguous. If you give me the error, I can tell you how the query is supposed to look like.
I’d still go for the first approach with the VIEWs as for now, until the plugin gets updated, you can not filter the result of the query via Advanced, this is a bug/limitation. So if you want to filter your JOINs (that’s what this “usage” of foreign key references is called), you go with views.
@Broberto Thanks so much sounds good. How that would show up in Weweb for example as a collection? Would all of the variables from the 2 databases be joined into 1 collection response? As in liek this still"?
I’m not 100% sure If I have set htis up right, as the VIEW is not getting any data. So, it has slightly changed from when I posted this originally, but here is the SQL that you gave me customized.
Note: The table is called “user access” and its sole purpose it do denote a users connection to an account.
I now see the view in Weweb. HOWEVER, in weweb, it is not finding any relationships between the user_access table and the accounts table, and there is definitely supposed to be at least 1 showing up. Im getting this error:
Could you copy and paste the whole error? Does the joined view show properly in Supabase?
If you sent me your tables structure and how they’re referenced, that would be cool, no need to send data, just the columns
Ok @Broberto I think my mistake was adding the DB’s into supbase all with a capital letter at the front so I have “case sensitivity” issues LOL!
So here is what happened. I tried to use the supabase AI generator to fix it and it must have created a new table called “accounts” in lower case in the process. It was referencing that (which was empty)…
My solution was to go ahead and change the SQL to the correct case and it seems to be finding a match now!
Sorry for that confusion (hindsight, I should have made all of my DB’s in all lowercase)…
