I have this user_id which works as a FK for both tables of station and types .

Data definition:

create table
  public.types (
    id uuid not null default uuid_generate_v4 (),
    name text not null,
    price numeric(10, 2) not null,
    user_id uuid not null,
    constraint types_pkey primary key (id),
    constraint types_user_id_fkey foreign key (user_id) references profiles (id) on update cascade on delete restrict
  ) tablespace pg_default;

        INSERT INTO types (id, name, price, user_id)
VALUES
  ('b6222d4b-5322-4b52-b80a-bb93759d2f6d', 'Type 1', 50, 'd5f71d08-abcd-45dd-9fb3-98bead9acfd3'),
  ('8fcda75e-d75d-4e18-a143-aeeec08a1fb1', 'Type 2', 25, 'cf336746-351b-4b1f-890e-7c7716472fe2'),
  (gen_random_uuid(), 'Type 1.2', 60, 'd5f71d08-abcd-45dd-9fb3-98bead9acfd3'),
  (gen_random_uuid(), 'Type 1.3', 70, 'd5f71d08-abcd-45dd-9fb3-98bead9acfd3'),
  (gen_random_uuid(), 'Type 1.4', 20, 'd5f71d08-abcd-45dd-9fb3-98bead9acfd3');

create table
  public.station (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null default auth.uid (),
    station_name text not null,
    address text null,
    constraint station_pkey primary key (id),
    constraint station_user_id_key unique (user_id),
    constraint station_user_id_fkey foreign key (user_id) references profiles (id) on update cascade on delete cascade
  ) tablespace pg_default;



  INSERT INTO public.station (id, user_id, station_name, address)
VALUES
  ('6cf80bd7-633d-4bc3-8782-b69688e4c98d', '2521f577-9076-450d-b03d-1ab43b6354e6', 'Jennie', '12, Aaa, 7'),
  ('1f079d4b-492f-438a-a842-292ce8e954a1', 'd5f71d08-abcd-45dd-9fb3-98bead9acfd3', 'My Station', '12, Street name, 7');

What I want to achieve it to get all of the water types and station with the same user_id. So if I'll try to retrieve the types and station that belongs to user2. I would need the data of user2 which would have this:

user2

'type2',20
'type 18', 300
 'station2'
 'Brazil`

I think it would be something like this, but how can I do this in Supabase

 select
  station,
  type
from
  station
  join station.user_id = type.user_id
where
  station.user_id = '2d6c72a1-175c-4c08-823e-10e35c0d1d7f'

UPDATE: The reason I cannot query is that the reference for both types and table was from the auth.users which is not PUBLIC. Hence, I have now updated my tables to this:

Added a profiles table:

create table
  public.profiles (
    id uuid not null,
    email text null,
    full_name text null,
    constraint profiles_pkey primary key (id),
    constraint profiles_email_key unique (email),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on delete cascade
  ) tablespace pg_default;

I have also already updated the FK for both the types and station. However, my problem still persists. I do not know how I can retrieve the data for multiple tables that share the same user_id.

I tried this:

const { data, error } = await supabase
      .from('profiles')
      .select(`
        id,
        station: user_id (station_name),
        types(
          *
        )
      `)
      .eq('user_id',searchParams.id);

And tried this as well but got an error that says: Could not find a relationship between 'station' and 'type' in the schema cache error.

const {data, error} = await supabase  
        .from('station')
        .select(`
          station_name,
          user_id,
          profiles(
            id
          ),
          type (
            user_id
          )
        `)
        .eq('user_id', searchParams.id)

I am already sure that the searchParams.id is not null and does retrieve the right user id.

CodePudding user response：

Your SQL query is fundamentally correct for joining two tables that share the same foreign key. However, there are a few things you might want to consider for accuracy and performance:

1. Ensure the WHERE clause uses the correct type for user_id (in your case, it should be a UUID, not an integer).

2. Use aliases for tables to make your query more readable and to avoid ambiguity.

3. Select only the columns you need instead of * to improve performance, especially if there are many columns.

4. Since user_id is a UUID, you should use a UUID in your query instead of 123.

Assuming auth.users is the table that user_id references, and you're looking for the rows where auth.users.id equals a specific UUID, your query in Supabase or standard SQL would look something like this:

SELECT 
    station.*,
    types.*
FROM 
    station
INNER JOIN types ON station.user_id = types.user_id
WHERE 
    station.user_id = 'your-uuid-value-here';

If you are querying directly in Supabase, you would execute this SQL statement in their SQL editor. If you are doing this from an application using Supabase's client libraries, you would need to construct a query using the library's functions, which might look different syntactically from raw SQL.

Here's an example of how you might do this using Supabase's JavaScript client:

const { data, error } = await supabase
  .from('station')
  .select(`
    *,
    types (
      *
    )
  `)
  .eq('user_id', 'your-uuid-value-here');

This example assumes you're using JavaScript and have already initialized supabase with your project's details. The select method is used to specify the related types entries you want to retrieve alongside station.

CodePudding user response：

const {data, error} = await supabase  
    .from('profiles')
    .select(`
      *,
      station (
        station_name, user_id
      ),
      type (
        *
      )
    `)
    .eq('id', searchParams.id)

What I needed to do was actually replace the from profiles because it has the reference for the user_id for both station and types