SupabaseS
Supabase•12mo ago
AndyJessop

Many-to-many Relation is not Detected

I have the following tables:
agents => uuid, ...
channels => uuid, ...
agent_channel: uuid, agent_uuid (fk), channel_uuid (fk)

After following the docs on many-to-many joins here: https://supabase.com/docs/guides/database/joins-and-nesting?queryGroups=language&language=js#many-to-many-joins, I'm trying to select them like this:

const { data, error } = await this.#client
    .from("channels")
    .select("*,agents(*)")
    .order("created_at", { ascending: true });


But I get this error:

code:"PGRST200"
details:"Searched for a foreign key relationship between 'channels' and 'agents' in the schema 'public', but no matches were found."
hint:"Perhaps you meant 'agent_channel' instead of 'agents'."
message:"Could not find a relationship between 'channels' and 'agents' in the schema cache"


What am I doing wrong here? The only thing I can see is that it's a naming issue 
id
 vs uuid
  • is this a requirement?
ncidentally, the type inference is correct. If I hover data, I get this (which is correct):

const data: {
    created_at: string
    title: string     
    updated_at: string | null     
    uuid: string     
    agents: {         
        agent_type_uuid: string         
        created_at: string         
        display_name: string         
        system_prompt: string         
        updated_at: string | null         
        uuid: string     
    }[]
}[] | nul
Querying Joins and Nested tables | Supabase Docs
The Data APIs automatically detect relationships between Postgres tables.
Was this page helpful?