How to investigate "No relationship between table1 and table2" error

[richcorbs]

I think I've created my relationships correctly with foreign keys and references but my supabase client request fails with

Could not find foreign keys between these entities. No relationship found between table1 and table2

It used to work but I must have changed something.

How do I investigate this further?

It would be REALLY nice to have a way in the UI to see the configured relationships, potential issues, possible resolutions, and hints for adding/removing relationships.

[kiwicopple]

Hey @richcorbs - let us know the project ID and we'll see what we can do. Sometimes it requires a PostgREST restart to detect the relationships. This is automated, so it's strange that it's not picking up yours - it suggests that PostgREST didn't restart when you changed your schema.

It would be REALLY nice to have a way in the UI to see the configured relationships, potential issues, possible resolutions, and hints for adding/removing relationships

We're working on better support for this now, trying to make it as easy as possible for developers to solve issues directly in the dashboard

[richcorbs]

I think the id is jjgonckxlaoknptxqpfy.

This is the query that fails:

const { body } = await supabase.from('events')
    .select("*,event_teams(*,teams(*)),races(*,registrations!registrations(*),finishers(*))")
    .order('events.starts_at', true).order('configurations.name', true)
    .order('finishers.time_in_seconds.asc', false)

[steve-chavez]

Hi @richcorbs.

Looks like you have two relationships between races and registrations - a one-to-many and a many-to-many. In these cases, you need to disambiguate the request for getting your desired response.

If you do:

supabase.from('events').select("*,event_teams(*,teams(*)),races(*,registrations(*),finishers(*))")

Then you'll get the error:

{
  "hint": "By following the details key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)",
  "details": [
    {
      "relationship": "fk_race[id][race_id]",
      "origin": "public.races",
      "cardinality": "o2m",
      "target": "public.registrations"
    },
    {
      "relationship": "public.finishers[fk_race][fk_registration]",
      "origin": "public.races",
      "cardinality": "m2m",
      "target": "public.registrations"
    }
  ],
  "message": "More than one relationship was found for races and registrations"
}

(Here o2m is one-to-many and m2m is many-to-many)

For clearing the error, you can choose between these two requests:

supabase.from('events').select("*,event_teams(*,teams(*)),races(*,registrations!fk_race(*),finishers(*))")
//or
supabase.from('events').select("*,event_teams(*,teams(*)),races(*,registrations!finishers(*),finishers(*))")

Note that registrations!registrations(..) won't hint to any relationship. That's why you got the Could not find foreign keys between these entities error.

Hope that helps!

---

More details about this can be found on PostgREST Embedding Disambiguation docs.

[richcorbs]

How can you tell that I have two relationships between races and registrations?

I only want a race to have many registrations (via registrations.race_id).

[richcorbs]

I should also say "thank you"! The !fk_race made the difference.

I only remember moving an fk constraint from a table and that query was working prior to removing the fk constraint.

Where is the second relationship coming from? Is it races -> finishers -> registration? How can I avoid that?

What I want ultimately is:

• race -> registrations (one-to-many)
• race -> finishers (one-to-many)
• finisher -> registration (one-to-one)

[steve-chavez]

I should also say "thank you"! The !fk_race made the difference.

Great! If it makes things more clear, you can also use the fk column(race_id) instead of the fk name:

supabase.from('events').select("*,event_teams(*,teams(*)),races(*,registrations!race_id(*),finishers(*))")

How can you tell that I have two relationships between races and registrations?

We look at the foreign keys and possible junction tables for finding the relationships.

Where is the second relationship coming from? Is it races -> finishers -> registration?

In this case, since finishers has an fk to races and an fk to registrations, it's being detected as a junction table for a many-to-many relationship: races -> finishers -> registration.

How can I avoid that?

Right now we auto-generate the relationships by introspecting your schema, but we'll be looking at how to make them customizable.

For now, the hint(!race_id or !fk_race) is your best bet :)