How do I perform a join supabase statement in python code

Question

Below is a code where I am trying to perform a join operation. I have a table citizen which has a foreign key location_id from table location. Now i want to retrieve the the name of location when of the citizen using the Location Id:

@app.get("/join")
async def get_citizens_location():
    response = supabase.table('citizens').select('firstname').execute()
    return response

How do I solve this, are there alternative?

@app.get("/join")
async def get_citizens_location():
    response = supabase.table('citizens').select('firstname','location:name').leftJoin('citizen:location_id','location:id').execute()
    return response

I have tried this so far but it looks like Supabase does not have left join method or class

Answer 1

You can join tables easily in supabase with the help of foreign keys.

What I have inferred from your question that, you have two tables citizen and location with a foreign key location_id in the table citizen.

To join these two tables, you can use the following code (assuming that you want all matched data from these two tables):

response = supabase.table('citizen')
  .select('*, location(*)')
  .execute()

Reference to the documentation