GetDutchie
Query association with WherePhrase doesn't work with Supabase
Issue with WherePhrase and association columns in Brick
Based on the test:
https://github.com/GetDutchie/brick/blob/063b85324b1c7d28aa12bd9af1319917e677a912/packages/brick_sqlite/test/query_sql_transformer_test.dart#L366-L387
When I apply similar logic in my code, I get an error from Supabase:
Query(
where: [
Where.exact("group", Or("creatorId").isExactly(localId)),
Where.exact("person", WherePhrase([
const Or("id").isExactly(profileId),
const Or("cloudId").isExactly(cloudId)
]), isRequired: false)
],
)
The error I receive:
I/flutter (5044): OfflineFirstRepository: WARNING: #hydrate supabase failure: PostgrestException(message: column _group_members_.cloud_id does not exist, code: 42703, details: Bad Request, hint: Perhaps you meant to reference the column "_group_members_.group_id".)
Observation: Brick seems to interpret the columns inside the WherePhrase as belonging to the parent table, not the associated table. This causes invalid SQL when querying associations.
Work around
Query without WherePhrase in association query
Query(
where: [
Where.exact("group", Or("creatorId").isExactly(localId)),
Where.exact("person", Or("id").isExactly(profileId)),
Where.exact("person", Or("cloudId").isExactly(cloudId)),
],
)
Without knowing your schema, _group_members_ doesn't seem to be person?
Query(
where: [
Where.exact("group", Or("creatorId").isExactly(localId)),
Where.exact("person", Or("id").isExactly(profileId)),
Where.exact("person", Or("cloudId").isExactly(cloudId)),
],
)
Wait, this does work? I thought based on #649 it doesn't work?
What is the Supabase PostgREST URL generated from the query? Is the URL structure wrong?
Wait, this does work? I thought based on https://github.com/GetDutchie/brick/issues/649 it doesn't work?
@tshedor This query does work now, not sure what I did to fix it, lots of changes in my code since then. :)
About the query from your test case...
final groupMembers = await appDB.get<GroupMember>(query: Query(
where: [
Where.exact("group", Or("creatorId").isExactly(currUser.localId)),
Where.exact("person", WherePhrase([
const Or("id").isExactly(currUser.profileId),
const Or("cloudId").isExactly(currUser.cloudId)
]), isRequired: false)
],
));
Error
I/flutter (22728): OfflineFirstRepository: WARNING: #hydrate supabase failure: PostgrestException(message: column _group_members_.cloud_id does not exist, code: 42703, details: Bad Request, hint: Perhaps you meant to reference the column "_group_members_.group_id".)
Query on Supabase:
{
"select": "id,group:groups!group_id(id,creator_id,name,created_at,memberLinks:_group_members_!group_id(*)),group_id,person:persons!person_id(id,cloud_id,local_contact_id,creator_id,created_at,first_name,last_name,nick_name,email,date_of_birth),person_id,member_role",
"groups.creator_id": "eq.usr938621311us-199be7c6f04",
"group": "not.is.null",
"or": "(id.eq.prs828059584-199be7c6f1a-88ac81, cloud_id.eq.40954afa-5f2b-49b9-b57e-e8cd177c91f6)"
}
The "or" clause should be something like
"or": "(persons.id.eq.prs828059584-199be7c6f1a-88ac81, persons.cloud_id.eq.40954afa-5f2b-49b9-b57e-e8cd177c91f6)"
Noted I am querying Many-to-Many table