Nested "OR" Condition/Operation Support in Repository Where condition

[sthanase]

Feature Description

I would like to achieve the below using repository.where method

Select * from user where (firstName like "%Mike% OR firstName IN ("King", "Kong")) AND (lastName like "%Mike% OR lastName IN ("King", "Kong"))

I am aware this can be acheived through query builder and raw queries but currently there seems to no way to achieve this using the repository.where method. Is it a good idea to introduce a OR operator similar to the existing AND operator to achieve the above.

The Solution

Introduce OR operator similar to the AND operator to do something similar like below.

await repository.find(
where: {
firstName: OR(Like('%Mike%'), In('King','Kong')),
lastName: OR(Like('%Mike%'), In('King','Kong')),
},
})

Considered Alternatives

This can be achieved through query builder and raw queries.

Additional Context

No response

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[iJhefe]

"OR" are handled with array in where, so you can do:

await repository.find(
  where: [{
    firstName: Like('%Mike%'),
    lastName: Like('%Mike%'),
  }, 
  {
    firstName: In('King','Kong')
    lastName: In('King','Kong')
  }
],
})

but also I agree that we should have an OR operator

[sthanase]

"OR" are handled with array in where, so you can do:

await repository.find(
  where: [{
    firstName: Like('%Mike%'),
    lastName: Like('%Mike%'),
  }, 
  {
    firstName: In('King','Kong')
    lastName: In('King','Kong')
  }
],
})

but also I agree that we should have an OR operator

Your suggestion will not work for all scenarios. Your solution mixes "OR" and "AND" and may not yield the results we are expecting.

[Lqvrent]

"OR" are handled with array in where, so you can do:

await repository.find(
  where: [{
    firstName: Like('%Mike%'),
    lastName: Like('%Mike%'),
  }, 
  {
    firstName: In('King','Kong')
    lastName: In('King','Kong')
  }
],
})

but also I agree that we should have an OR operator

I have something like this (using MySQL):

options.where = [
  { // Search for a text in multiple columns
    name: Like("%...%"),
    description: Like("%...%"),
  },
  { // But only in someone entities
    company: {
      user: { id: X }
    }
  }
]

But it seems like the first part is completely ignored. Whatever I search, he is returning all element with company->user->id == X

+1 for this issue.