Filtering through foreign table doesn't respect join hint syntax

[notjustinshaw]

I have a query in supabase-js that works, and when I translate it to supabase-py it doesn't work. I am on the latest version of each. From what I could gather while poking around, the python version doesn't seem to respect the join-hint PostgREST syntax for referencing tables with foreign keys.

Javascript

supabase
  .schema('pfr')
  .from('games')
  .select(`id,
    date,
    away_score,
    home_score,
    away_team:team_seasons!fk_away_team_id!inner(city, name),
    home_team:team_seasons!fk_home_team_id!inner(city, name),
  `)
  .ilike('away_team.name', awayTeam)
  .ilike('home_team.name', homeTeam);

Python

supabase
  .schema('pfr')
  .table('games')
  .select('''id,
    date,
    away_score,
    home_score,
    away_team:team_seasons!fk_away_team_id!inner(city, name),
    home_team:team_seasons!fk_home_team_id!inner(city, name),
  ''')
  .ilike('away_team.name', awayTeam)
  .ilike('home_team.name', homeTeam);

Schema

The schema is what you would expect; a games table with two foreign keys each pointing at a team_season.

Error Message

In PostgREST, you are supposed to be able to reference aliased foreign keys like this. It shouldn't assume the column is a column on the games table. Instead it should use the join-hint to compute a path to that table based on the constraints/keys available between the games table and team_seasons table (in this case, there are two paths: fk_home_team_id and fk_away_team_id).

APIError: {'code': '42703', 'details': None, 'hint': None, 'message': 'column games.away_team.name does not exist'}

[silentworks]

Please provide a reproducible example repo to test this in. The repo should include some data and a database schema.

[silentworks]

@teodos this is not related and you should also provide your table details when posting. Your error is making reference to applications but your table is called applicants. Without seeing the tables we don't know what issue you could be having. Also open a separate issue with all the details I mentioned in this post.

[silentworks]

@teodos the docs clearly states how the .or_ operator works and you aren't following it's syntax. As I've mentioned before your issue is not related to the OPs issue, you should open your own issue and stop adding to this one.

[notjustinshaw]

I’ll see if I can find time to set up an example. The simplest case I can think of is the messages example from the js docs with a users table and a message table that references users twice, once for sender and once for recipient.

…

On Nov 6, 2024 at 4:13 PM -0800, Andrew Smith ***@***.***>, wrote: @teodos the docs clearly states how the .or_ operator works and you aren't following it's syntax. As I've mentioned before your issue is not related to the OPs issue, you should open your own issue and stop adding to this one. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: ***@***.***>

[silentworks]

@notjustinshaw I tested the message example from the docs and didn't get any issue with it.

[silentworks]

@notjustinshaw here is a screen recording of me testing the sample data you mentioned in the docs with the Python client.

Turn your volume down as I might be a bit loud when talking in the video.

supabase_py_983.mp4