I'm trying to filter for "GymGroups" which have "GymOutlets" that satisfy either of two conditions: either their name matches a substring, or their address matches a substring.
I tried this, but it simply returns all entries from "GymGroups" (although a single where
works as expected):
await this.gymGroupModel
.query()
.whereExists(
this.gymGroupModel
.relatedQuery('gymOutlets')
.where('address', 'ilike', `%${substring}%`)
.orWhere('name', 'ilike', `%${substring}%`),
)
.orderBy('name', 'ASC');
However, this instead works as expected:
await this.gymGroupModel
.query()
.whereExists(
this.gymGroupModel
.relatedQuery('gymOutlets')
.where('name', 'ilike', `%${substring}%`),
)
.orWhereExists(
this.gymGroupModel
.relatedQuery('gymOutlets')
.where('address', 'ilike', `%${substring}%`),
)
.orderBy('name', 'ASC');
Furthermore, making a query on GymModel itself like in the first method does return the expected results:
await this.gymModel
.query()
.where('address', 'ilike', `%${substring}%`)
.orWhere('name', 'ilike', `%${substring}%`);
So why does the first method not work, and how do I properly do this?