Repository find method where option could not combine conditions properly

[hyt658]

Issue description

Lack of brackets for the conditions in where when translated to the real query in DB and connected with AND

Expected Behavior

If I do:
dataSource.getRepository(Gift).find({where: { cond1: "...", cond2: "..." })
In the database it should be:
SELECT gift_id from Gifts where (cond1) AND (cond2)

Actual Behavior

In the database it actually is:
SELECT gift_id from Gifts where cond1 AND cond2

Steps to reproduce

Assume table Gift has start_date and end_date represents the available period for users to earn the gift. Now I input user's year and month to check what gifts he can earn. Code:

I have to use brackets to enclose the entire Raw sentence to make sure these two conditions are paralleled, otherwise the AND and OR will mess up

My Environment

Dependency	Version
Operating System	MacOS
Node.js version	20.6.0
Typescript version	5.2.2
TypeORM version	0.3.17

Additional Context

Postgresql log:

2023-12-02 11:32:21.276 EST [25970] LOG:  duration: 2.879 ms  parse <unnamed>: SELECT "Gift"."gift_id" AS "Gift_gift_id", "Gift"."content" AS "Gift_content", "Gift"."type" AS "Gift_type", "Gift"."quantity" AS "Gift_quantity", "Gift"."start_date" AS "Gift_start_date", "Gift"."end_date" AS "Gift_end_date", "Gift"."category" AS "Gift_category", "Gift"."image" AS "Gift_image" FROM "gifts" "Gift" WHERE (EXTRACT(YEAR FROM "Gift"."start_date") < $1) OR 
	                (EXTRACT(YEAR FROM "Gift"."start_date") = $2 AND EXTRACT(MONTH FROM "Gift"."start_date") <= $3) AND (EXTRACT(YEAR FROM "Gift"."end_date")) > $4 OR 
	                (EXTRACT(YEAR FROM "Gift"."end_date") = $5 AND EXTRACT(MONTH FROM "Gift"."end_date") >= $6) LIMIT 20
2023-12-02 11:32:21.278 EST [25970] LOG:  duration: 2.153 ms  bind <unnamed>: SELECT "Gift"."gift_id" AS "Gift_gift_id", "Gift"."content" AS "Gift_content", "Gift"."type" AS "Gift_type", "Gift"."quantity" AS "Gift_quantity", "Gift"."start_date" AS "Gift_start_date", "Gift"."end_date" AS "Gift_end_date", "Gift"."category" AS "Gift_category", "Gift"."image" AS "Gift_image" FROM "gifts" "Gift" WHERE (EXTRACT(YEAR FROM "Gift"."start_date") < $1) OR 
	                (EXTRACT(YEAR FROM "Gift"."start_date") = $2 AND EXTRACT(MONTH FROM "Gift"."start_date") <= $3) AND (EXTRACT(YEAR FROM "Gift"."end_date")) > $4 OR 
	                (EXTRACT(YEAR FROM "Gift"."end_date") = $5 AND EXTRACT(MONTH FROM "Gift"."end_date") >= $6) LIMIT 20
2023-12-02 11:32:21.278 EST [25970] DETAIL:  parameters: $1 = '2023', $2 = '2023', $3 = '3', $4 = '2023', $5 = '2023', $6 = '3'
2023-12-02 11:32:21.278 EST [25970] LOG:  execute <unnamed>: SELECT "Gift"."gift_id" AS "Gift_gift_id", "Gift"."content" AS "Gift_content", "Gift"."type" AS "Gift_type", "Gift"."quantity" AS "Gift_quantity", "Gift"."start_date" AS "Gift_start_date", "Gift"."end_date" AS "Gift_end_date", "Gift"."category" AS "Gift_category", "Gift"."image" AS "Gift_image" FROM "gifts" "Gift" WHERE (EXTRACT(YEAR FROM "Gift"."start_date") < $1) OR 
	                (EXTRACT(YEAR FROM "Gift"."start_date") = $2 AND EXTRACT(MONTH FROM "Gift"."start_date") <= $3) AND (EXTRACT(YEAR FROM "Gift"."end_date")) > $4 OR 
	                (EXTRACT(YEAR FROM "Gift"."end_date") = $5 AND EXTRACT(MONTH FROM "Gift"."end_date") >= $6) LIMIT 20
2023-12-02 11:32:21.278 EST [25970] DETAIL:  parameters: $1 = '2023', $2 = '2023', $3 = '3', $4 = '2023', $5 = '2023', $6 = '3'
2023-12-02 11:32:21.279 EST [25970] LOG:  duration: 0.444 ms

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.

[alenap93]

@hyt658, you have nested conditions in your Raw, so i think that this behaviour is correct, and you have to set parenthesis around conditions...

[hyt658]

@alenap93 thanks for your reply. I think when listing several conditions in where: {...}, it should be expected that they will be linked parallelly with and (even if they are nested). For example, in C/C++, if we want to create a macro to do MIN operation between two numbers:

#define min(x, y) ((x)>(y) ? (y) : (x))

We have to enclose x and y in parentheses because x and y maybe expressions instead of single numbers. I know it might not be correct to use C/C++ logic to think of typescript, but what I mean is, adding parentheses for conditions does not have any bad influence and it can avoid potential issues.

[esdrasbrz]

The same problem occurs with findBy, for instance:

const result = await database
  .getRepository(Gift)
  .findBy({
    startDate: MoreThanOrEqual(initialDate),
    endDate: Or(
      LessThanOrEqual(finalDate),
      IsNull(),
    ),
  })

generates the following SQL:

SELECT ... FROM gift
WHERE (start_date >= $1 AND end_date <= $2 OR end_date IS NULL)

instead of:

SELECT ... FROM gift
WHERE (start_date >= $1 AND (end_date <= $2 OR end_date IS NULL))

This is a very unexpected behavior, and I think it should automatically add the parenthesis around conditions, especially using Or and And.

Also, is it even possible to change the typescript code to make it include the parenthesis around the Or? I had to make a query similar to this one with Query Builder instead because I couldn't find a way to fix the conditions, but it would be much easier to use findBy.

[alenap93]

@esdrasbrz i will work for this fix in the next days

[esdrasbrz]

@alenap93 thanks!