Repository find method where option could not combine conditions properly #10534
Description
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.