Joining tables to retrieve data

[praj18]

I have two tables, one for domains and one for webpages where:

 domains (
  id bigint generated by default as identity primary key,
  owner_id uuid references auth.users not null,
  domain_uid varchar not null,
  domain_name text,
  domain_address text,
  created_at timestamp
);

 webpages (
  id bigint generated by default as identity primary key,
  domain_id bigint references domains.id not null,
  title text,
  slug text,
  created_at timestamp
);

I want to be able to do the following using supabase-js:

SELECT
    *
FROM
    webpages w
JOIN domains d 
    ON d.id = w.domain_id
WHERE
    d.domain_uid = 'xxxxxxxxxxxxx';

I've tried various solutions, however, I can't seem to get it to work.
And my current work around is by doing the following...

  const data = await supabaseAdmin
    .from('domains')
    .select('id, domain_name')
    .eq('domain_uid', id)
    .single()
    .then(async (domainData) => {
      const domainName = domainData?.data?.domain_name;
      const { data, error } = await supabaseAdmin
        .from('webpages')
        .select('*')
        .eq('domain_id', domainData?.data?.id);

      return { domainName, data };
    });

I don't think using promises is the ideal way of doing this.
I'd be really grateful if someone could help me figure out a better way of doing this

[dshukertjr]

Hi @praj18!

I noticed that you are using single() on the query of domains. Could I assume that in this case you only want single record for domain, and you have multiple webpages?

In that case, could you try the following code and let me know how it goes?

const { data, error } = await supabase
  .from('domains')
  .select(`
    *,
    webpages (*)
  `)
  .eq('domain_uid', id)
  .single()

This should return results in the form of

{
  id: '[domain_id]',
  domain_name: '[domain_name]',
  ...
  webpages: [
    {
      id: '[webpage_id]',
      domainn_id: '[domain_id]',
      ...
    },
    {
      id: '[webpage_id]',
      domainn_id: '[domain_id]',
      ...
    },
  ]
}

[praj18]

Thank you so much!
I think I tried it the other way around so it gave me weird results, but this gives me the exact results I wanted. I assumed that using single() at the end would only return 1 webpage as well, but I was wrong.