Fiiltering through join values

[liang799]

I have a question similar to this

I was wondering if my following code is wrong, or it is not possible atm

# rivens table & registration table are linked by foreign keys
response = SupaClient.supabase.table("rivens").select('*, registration(guild_id)')\
    .eq('registration.guild_id', author.guild.id).is_("revealed_weapon", "NULL").execute()

Error message

.....None, 'message': 'column rivens.registration.guild_id does not exist'}

Originally posted by @liang799 in https://github.com/supabase-community/supabase-py/discussions/263

[J0]

Moving to postgrest-py - will have to take a closer look when a slot frees up

[guillego]

Hello! I found this same issue today when porting (supabase/supabase#12532) one of the official examples from the js client (filtering through foreign tables).

This is the behavior I saw:

>>> from urllib.parse import unquote_plus
>>> request = supabase.table('cities').select('name,countries(*)').eq('countries.name','Estonia')
>>> response = request.execute()
postgrest/_sync/request_builder.py", line 67, in execute
    raise APIError(r.json())
postgrest.exceptions.APIError: {'code': '42703', 'details': None, 'hint': None, 'message': 'column cities.countries.name does not exist'}

>>> request_params = unquote_plus(str(request.params))
>>> request_params
'select=name,countries(*)&"countries.name"=eq.Estonia'

It seems like it's adding extra " into the request that the postgrest docs don't show.

In fact if I manually add the request parameters, it works:

>>> request.params.items()
dict_items([('select', 'name,countries(*)'), ('"countries.name"', 'eq.Estonia')])
>>> request.params = request.params.remove('"countries.name"')
>>> request.params
QueryParams('select=name%2Ccountries%28%2A%29')
>>> request.params = request.params.add('countries.name','eq.Estonia')
>>> request_params = unquote_plus(str(request.params))
>>> request_params
'select=name,countries(*)&countries.name=eq.Estonia'
>>> response = request.execute()
>>> response
APIResponse(data=[{'name': 'Bali', 'countries': None}, {'name': 'Munich', 'countries': None}], count=None)

Seeing this I dag through the code and found where the filtering parameters are sanitized https://github.com/supabase-community/postgrest-py/blob/ea579fd782e7d4ef13820356c8cc7fba0a4bec86/postgrest/base_request_builder.py#L228
https://github.com/supabase-community/postgrest-py/blob/ea579fd782e7d4ef13820356c8cc7fba0a4bec86/postgrest/utils.py#L14-L19

This function adds double quotes to the filtering if a . is detected as one of the parameters.

Why is this functionality applied here? It seems to be interfering with the request.

Let me know what you think @J0, I'd be happy to add tests for these cases but I'm not sure if this sanitize_param() function can be safely removed or if it should be changed in some other way.

[J0]

As I understand, the sanitize_param function was originally added so that we could handle special characters. However, it seems like '.' isn't handled properly which causes an issue - this is a bug.

Slightly more context can be found here

If you have the bandwidth, feel free to add tests and remove '.' from being escaped.

[guillego]

@J0 I think this is closed, right? 😃

[J0]

Yup @guillego, thanks for the fix!