MySQL queries using double quotes instead of backtick

[hygison]

When I run a normal findAll

@JsonApi(User, { allowMethod: excludeMethod(['deleteRelationship']), requiredSelectField: false })
export class UsersController extends JsonBaseController<User> {
	@InjectService() public jsonApiService: JsonApiService<User>;
	@Inject(HttpService) protected httpService: HttpService;

	@Get()
	async getAll(@Query() query: QueryParams<User>) {
		console.log(this.jsonApiService);
		return this.jsonApiService.getAll({ query: query });
	}
}

It gives me an error:

[Nest] 349  - 11/10/2023, 8:06:06 AM   DEBUG [ErrorInterceptors] HttpException: Http Exception

When I check the ormLogs I get error in the query:

[2023-11-10T08:14:30.784Z][FAILED QUERY]: SELECT `countResult`.`id` AS `countResult_id` FROM `users` `countResult` INNER JOIN (SELECT `User`.`id` AS `subQueryId` FROM `users` `User` WHERE `User`.`deleted_at` IS NULL ORDER BY `User`.`id` ASC LIMIT 20) `subQuery` ON "subQuery"."subQueryId" = `countResult`.`id` WHERE `countResult`.`deleted_at` IS NULL
[2023-11-10T08:14:30.784Z][QUERY ERROR]: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."subQueryId" = `countResult`.`id` WHERE `countResult`.`deleted_at` IS NULL' at line 1

When I run the query on the sql directly it gives me an error, but If I just replace " with ``` it works fine.

My configs:

const typeOrmConfig: TypeOrmModuleOptions = {
	type: 'mysql',
	host: process.env.DB_HOST,
	port: parseInt(process.env.DB_PORT),
	username: process.env.DB_USER,
	password: process.env.DB_PASSWORD,
	database: process.env.DB_NAME,
	entities: [join(__dirname, '../entities/*.entity{.ts,.js}')],
	migrations: [join(__dirname, '../migrations/*{.ts,.js}')],
	synchronize: true,
	logging: true,
	logger: 'file',
	maxQueryExecutionTime: 1000,
	retryAttempts: 10,
	retryDelay: 3000

I was checking the library and it seems the error to be on : json-api-nestjs/src/lib/mixin/service/typeorm/methods/get-all/get-all.ts
Where we have: "subQuery"."${subQueryIdAlias}" = ${countAlias}.${primaryColumn}

[klerick]

sorry, but I didn't test with Mysql

[btibarra]

@hygison did you find a solution? I can't access the getAll routes either with mysql

[hygison]

@btibarra I know how to fix the specific error that I have on the nestjs-json-api library.
But can you show me the error you get?

@klerick I am considering 2 points:

• Change my db to Postgres
• Keep using MySQL and ask you permission to update your library when I get errors.

[btibarra]

@hygison I got the exact same error that you got with the " symbol instead of ` when running the mysql query on the get-all.ts file code.
I'm just starting a new project and was learning how to use this library so I just changed to postgres for the moment and it's working fine.

[klerick]

@hygison I'm working on new version and will try to check the compatibility with mysql. but full compatibility will be only with postgress. some operators like 'some' will work with postgress.

[klerick]

@hygison Could you check fix?
Could you change some code in code from node_modules?
'node_modules/json-api-nestjs/src/lib/mixin/service/typeorm/methods/get-all/get-all.js#113'
need change

.innerJoin(`(${builder.getQuery()})`, 'subQuery', `"subQuery"."${subQueryIdAlias}" = ${countAlias}.${primaryColumn}`)

to

.innerJoin(`(${builder.getQuery()})`, 'subQuery', `${builder.escape('subQuery')}.${builder.escape(subQueryIdAlias)} = ${countAlias}.${primaryColumn}`)

if will be ok, i push new version with fix.
I do not have Mysql server and Can't check myself%)

[hygison]

@klerick It worked perfectly. Thank you very much.

Should I close this issue or wait for the push?

[github-actions]

🎉 This issue is included in version [email protected] 🎉

The release is available on GitHub release