Mikro-ORM:错误:没有参数$1

Question

好吧，我一直在尝试搜索Mikro-ORM的文档，以了解如何传入原生sql查询参数，但我没有找到任何东西。在尝试了一下代码之后，这是它看起来的样子，我认为这是正确的，但我得到了这个错误

error: SELECT DISTINCT * FROM direct_messages WHERE receiver_id = $1 OR sender_id = $1 ORDER BY sent_at DESC - there is no parameter $1
    at Parser.parseErrorMessage (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/yonden/Documents/projects/matchup/server/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:400:28)
    at Socket.emit (domain.js:470:12)
    at addChunk (internal/streams/readable.js:290:12)
    at readableAddChunk (internal/streams/readable.js:265:9)
    at Socket.push (internal/streams/readable.js:204:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
  length: 94,
  severity: 'ERROR',
  code: '42P02',
  detail: undefined,
  hint: undefined,
  position: '60',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '907',
  routine: 'transformParamRef'
}

我的代码目前看起来像这样

@Query(() => String)
  async Conversations(@Ctx() { em, userData }: MyContext): Promise<string> {
    const connection = em.getConnection();
    const queryString = `SELECT DISTINCT * FROM direct_messages WHERE receiver_id = $1 OR sender_id = $1 ORDER BY sent_at DESC`;
    return await connection
      .execute(queryString, [userData['sub']])
      .then((results) => {
        console.log(results);
        return 'worked';
      })
      .catch((error) => {
        console.log(error);
        return 'Failed';
      });
  }

对于某些上下文，userData‘’sub‘是来自googleOAuth userID类型的字符串。谢谢!

Answer 1

在原始查询中，您需要使用?而不是$1。这就是底层查询构建器knex的工作方式。

const connection = em.getConnection();
const queryString = `SELECT DISTINCT * FROM direct_messages WHERE receiver_id = ? OR sender_id = ? ORDER BY sent_at DESC`;
return await connection
  .execute(queryString, [userData['sub'], userData['sub']])

或者，您可以使用knex的命名绑定，这与此类似(允许在查询中多次使用一个参数)：

// the `em` needs to be typed to `SqlEntityManager` to have the `getKnex` method
const knex = em.getKnex();
const qb = knex.raw('SELECT DISTINCT * FROM direct_messages WHERE receiver_id = :id OR sender_id = :id ORDER BY sent_at DESC', { id: userData['sub'] });
const res = await em.execute(qb);
// ...

或者，MikroORM查询构建器具有qb.raw()方法，它只是em.getKnex().raw()的快捷方式

https://github.com/mikro-orm/mikro-orm/blob/master/tests/QueryBuilder.test.ts#L1314-L1319