more join table examples needed

[eifo]

I could not get around the documentation to convert the following join example with aliases, I could create a view / function of course but I'm trying to wrap my head around supabase syntax, any help pls, the following is the function I'm trying to convert using pg driver:

const {rows} = await db.query(select c.*,trade_name as name,cou.name as country from companies c join countries cou on cou.id = c.country_id join company_user cu on cu.company_id = c.id where c.id = $1 and cu.user_id = $2,
[companyId,user_id])

[kiwicopple]

I'll trim your SQL a bit so it's more readable, but you should be able to change this:

select * 
from companies c
  join countries cou on cou.id = c.country_id
  join company_user cu on cu.company_id = c.id
where c.id = "XXX" and cu.user_id = "YYY"

to this:

const { data, error } = supabase
  .from('companies')
  .select(`
    *, 
    countries(*), 
    company_user(*)
  `)
  .eq('id', companyId)
  .eq('company_user.id', user_id)

The relationships should be auto-detected by PostgREST. Let me know if that doesn't work!

[eytanronen]

In this case there would be multiple returns with the same field name and throw an error, how do you alias for example multiple fields named "name" would it be just name as newName? It's hard to test these special syntax would have been great to have an syntax editor just like the sql editor. Its great that relationships are auto-detected!

[steve-chavez]

@eytanronen You can alias the embedded resources with alias:tbl. Like so:

const { data, error } = supabase
  .from('companies')
  .select(`
    *, 
    cntries:countries(*), 
    comp:company_user(*)
  `)
  .eq('id', companyId)
  .eq('comp.id', user_id)

[eifo]

Thanks, but is it possible to rename (alias) the returning values from the join? For example
...
cntries:countries(name:countryName)

If not possible then you have to change the schema.

[steve-chavez]

@elfo Yes, that's possible as well. But it should be alias:column, like:

const { data, error } = supabase
  .from('companies')
  .select(`
    *, 
    cntries:countries(countryName:name)
  `)

[eifo]

that answers it! thanks, I get the return of every join inside a new object is it possible to ...spread it so it's all in the same object