How to perform a JOIN in Supabase with my tables?

Question

I'm having a (probably easy to solve) problem with supabase, I'm still new to it and I was hoping someone here could help me.

I'm having trouble trying to join two columns that have the same auth.user_id. I made two public tables (I simplified it a bit so it's easier to understand): user_profile:

+---------+----------+
| user_id | username |
+---------+----------+
| 123AB   | JohnDoe  |
+---------+----------+

And user_stats:

+---------+------------+
| user_id | experience |
+---------+------------+
| 123AB   | 1500       |
+---------+------------+

Now when I was trying to follow supabase's documentation (https://supabase.com/docs/reference/javascript/select) I came up with this piece of code:

const { data } = await supabase
      .from("user_profile")
      .select(`
        username,
        user_stats (
          experience
        )
      `)
      .eq("user_id", userId)
      .single();

It did not work. I got an error that said that supabase had trouble finding a connection between the tables.

I came up with this solution; adding another column profile_id to user_profile and user_stats with a foreign key relationship between them:

+---------+------------+----------+
| user_id | profile_id | username |
+---------+------------+----------+
| 123AB   | 1          | JohnDoe  |
+---------+------------+----------+

+---------+------------+------------+
| user_id | profile_id | experience |
+---------+------------+------------+
| 123AB   | 1          | 1500       |
+---------+------------+------------+

This ended up working with my supabase code, but I'm still not convinced that this should be the right way to go. Wouldn't this create redundant data? In my mind I could've already made the connection with just the user_id column. Should I keep this as is? Maybe remove column user_id from user_stats?

Answer 1

You are trying to use one-to-one as I understand. One "user_profile" can have one "user_stat". Create a public "user_profile" table and add the "experience" column here. If you insist to sperate colums, try it:

public.user_profile
+---------+------------+----------+
| user_id | stat_id    | username |
+---------+------------+----------+
| 123AB   | 1          | JohnDoe  |
+---------+------------+----------+

public.user_stat
+---------+------------+
| id      | experience |
+---------+------------+
| 1       | 1500       |
+---------+------------+

Set the "stat_id" column as foreign and collect your data with

const { data, error } = await supabase
      .from("user_profile")
      .select(`
        username,
        stat_id(*) //Or stat_id(experience)
      `)
      .eq("user_id", userId)
      .single();

method in Javascript.