Android Question [Supabase] [SOLVED] Many-to-Many JOIN Table

[yiankos1]

Hello,

I want to execute this:

SELECT
    users.*,
    gym_users.id_role,
    gym_roles.name,
    gym_roles.level
FROM
    public.users
JOIN
    public.gym_users ON users.id = gym_users.id_user
JOIN
    public.gym_roles ON gym_users.id_role = gym_roles.id
WHERE
    users.id = ?

and i do this:

Query.Columns("*,gym_users(id_role),gym_roles(name,level)").From("users").Filter_Equal(CreateMap("id":id))

but it throws this error:

ResponseError. Reason: , Response: {"code":"PGRST200","details":"Searched for a foreign key relationship between 'users' and 'gym_roles' in the schema 'public', but no matches were found.","hint":"Perhaps you meant 'gym_users' instead of 'gym_roles'.","message":"Could not find a relationship between 'users' and 'gym_roles' in the schema cache"}

All foreign keys are set up properly.

If i run the sql code in editor, it runs properly!

Is there any way to run the sql command because Supabase API does not get the connection.

Thank you for your time.

UPDATE

Query.Columns("users(*),id_role,gym_roles(name,level)").From("gym_users").Filter_Equal(CreateMap("id_user":id))

getting the middle table(gym_users) which include id_user and id_role did the trick!