Nest.js + TypeOrm：原始SQL查询及其参数绑定
上一篇
DataSource.query 进行参数化原始SQL查询
使用 DataSource.query()，注意，如果是 PostgreSQL，则参数占位符不能使用问号 ?，只能使用 $n，并且在没有表名的情况下需要指定类型：
否则，会出现错误：PostgreSQL - ERROR: could not determine data type of parameter $1，参见：https://blog.51cto.com/u_10980859/5303844
@Injectable()
export class PermissionsService {
constructor(
private readonly dataSource: DataSource,
) { }
async check(id: number, body: CheckDTO) {
let sql = `SELECT $1::int4`;
let parameters = [id];
try {
let [row1] = await this.dataSource.query(sql, parameters);
console.log(row1);
} catch(e) {
// console.log(e);
}
return false;
}
}
关于绑定占位符的讨论：https://github.com/typeorm/typeorm/issues/881
取决于底层数据库驱动程序：
Oracle: query('SELECT * FROM table WHERE name = :name', [ { val: 'something' } ])
MySQL: query('SELECT * FROM table WHERE name = ?', [ 'something' ])
MSSQL: query('SELECT * FROM table WHERE name = @0', [ 'something' ])
Postgres: query('SELECT * FROM table WHERE name = $1', [ 'something' ])
DataSource.createQueryBuilder 进行参数化原始SQL查询
https://dev.to/avantar/how-to-output-raw-sql-with-filled-parameters-in-typeorm-14l4
PostgreSQL JSONB 运算符
https://www.postgresql.org/docs/9.4/functions-json.html#FUNCTIONS-JSONB-OP-TABLE
| Operator | Right Operand Type | Description | Example |
|---|---|---|---|
| @> | jsonb | Does the left JSON value contain within it the right value? | '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb |
| <@ | jsonb | Is the left JSON value contained within the right value? | '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb |
| ? | text | Does the key/element string exist within the JSON value? | '{"a":1, "b":2}'::jsonb ? 'b' |
| ?| | text[] | Do any of these key/element strings exist? | '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c'] |
| ?& | text[] | Do all of these key/element strings exist? | '["a", "b"]'::jsonb ?& array['a', 'b'] |
为什么强调这个？因为我一顿操作，结果发现其实只要一个 ?| 运算就可以解决问题：
@Injectable()
export class PermissionsService {
constructor(
private readonly dataSource: DataSource,
) { }
/** 添加SQL参数，并返回其占位符 */
private pushParam(parameters: any[], el: any): string {
return `$${parameters.push(el)}`;
}
async check(id: number, body: CheckDTO) {
let parameters = [];
let roles = ``;
if (body.roles && body.roles.length > 0) {
roles = `AND (${
body.roles.map(
it => `roles ? ${this.pushParam(parameters, it)}`
)
.join(' OR ')
})`
}
let sql = `SELECT 1 as pass FROM t_user
WHERE id = ${this.pushParam(parameters, id)}
${roles}`;
//let [{ pass }]= await this.dataSource.query(sql, parameters); // 可能因为 undefined 而解构失败
//console.log(pass);
//return pass === 1;
let [row]= await this.dataSource.query(sql, parameters);
return row?.pass === 1;
}
}
