按订阅获取文章列表

Question

我正在尝试做一个过滤器，我可以用它来获取订阅记录

实体‘订阅’

export class Subscription {
  @PrimaryColumn()
  id: string;
  @Column('uuid')
  userId: string;
  @Column('uuid')
  targetUserId: string;
  @CreateDateColumn()
  createdAt: Date;
}

过滤器

applyFilter(query: QueryArticle, qb: SelectQueryBuilder<Article>, userId?: string) {
    if (query.filter) {
      switch (query.filter) {
        ....
        case 'subscriptions':
          qb.select(
            `article.authorId WHERE targetUserId IN (SELECT targetUserId FROM Subscription WHERE userId=${userId})`,
          );
          break;
      }
    }
    return qb;
  }

SQL代码

Select * FROM article WHERE authorId=targetUserId IN (SELECT targetUserId FROM Subscription WHERE userId=userId)

错误

syntax error at or near "f5779e5" +3974ms
QueryFailedError: syntax error at or near "f5779e5"

我怎样才能让一个人关注的所有帖子都使用TypeORM？

提前感谢你的回答！

Answer 1

不要做你正在做的事情，。你在冒着SQL Injection的风险。如果您确实想进行手动查询，您可以执行manager.query

const output = manager.query('article."authorId" WHERE "targetUserId" IN (SELECT "targetUserId" FROM Subscription WHERE "userId" = :userId)',
  { userId: userId }
);

请注意第二个参数，它包含由:userId的

引用的参数。如果您使用模板字符串进行查询，那么您可能做错了什么。

如果你想使用QueryBuilder，那么它看起来会有点不同(更多关于QueryBuilder here的信息)

const output = articleRepo.createQueryBuilder('article')
  .select('article.authorId')
  .where('article."authorId" IN (SELECT "targetUserId" FROM subscription WHERE "userId" = :userId)',
    { userId: userId }
  )
  .getRawMany(); // If you remove the .select('article.authorId'), you can use .getMany()