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.

[teodos]

getting what may be a very similar issue

say I'm trying

query = supabase.table('applicants').select(
            '*, profiles(id, name, email)'
)

or_conditions = []  
or_conditions.append(f"profiles.name.ilike.%{keyword}%")
or_conditions.append(f"profiles.email.ilike.%{keyword}%")
query = query.or_(', '.join(or_conditions)

I'm getting {'code': 'PGRST100', 'details': 'unexpected "a" expecting "not" or operator (eq, gt, ...)', 'hint': None, 'message': '"failed to parse logic tree ((profiles.name.ilike.%.me%,profiles.email.ilike.%.me%)) which makes sense that .name' is not an operator then I've tried doing profiles->name and profiles->email, and in that case, I'm getting

{'code': '42703', 'details': None, 'hint': 'Perhaps you meant to reference the column "applications.profile_id".', 'message': 'column applications.profiles does not exist'}

[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.