Query association with WherePhrase doesn't work with Supabase

[KiddoV]

Issue with WherePhrase and association columns in Brick

Based on the test:

brick/packages/brick_sqlite/test/query_sql_transformer_test.dart

Lines 366 to 387 in 063b853

	test('OR', () async {
	const statement =
	'SELECT DISTINCT `DemoModel`.* FROM `DemoModel` INNER JOIN `DemoModelAssoc` ON `DemoModel`.assoc_DemoModelAssoc_brick_id = `DemoModelAssoc`._brick_id WHERE (`DemoModelAssoc`.id = ? OR `DemoModelAssoc`.full_name = ?)';
	final sqliteQuery = QuerySqlTransformer<DemoModel>(
	modelDictionary: dictionary,
	query: Query(
	where: [
	Where.exact(
	'assoc',
	WherePhrase([
	const Or('id').isExactly(1),
	const Or('name').isExactly('Guy'),
	]),
	),
	],
	),
	);
	expect(sqliteQuery.statement, statement);
	await db.rawQuery(sqliteQuery.statement, sqliteQuery.values);
	sqliteStatementExpectation(statement, [1, 'Guy']);
	});

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

[tshedor]

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?

[KiddoV]

Wait, this does work? I thought based on #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

[tshedor]

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