brick icon indicating copy to clipboard operation
brick copied to clipboard

Query association with WherePhrase doesn't work with Supabase

Open KiddoV opened this issue 3 months ago • 3 comments

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)),
      ],
    )

KiddoV avatar Oct 13 '25 13:10 KiddoV

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?

tshedor avatar Oct 24 '25 03:10 tshedor

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

KiddoV avatar Oct 24 '25 15:10 KiddoV

Is the model a Person itself? So it's many-to-many but it's also circular?

tshedor avatar Nov 01 '25 21:11 tshedor