Get Article list by subscription

Question

I am trying to make a filter with which I could get subscription records

Entity 'Subscription'

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

Filter

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 code

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

Error

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

How can I get all the posts of people followed by a person use TypeORM?

Thanks in advance for your answer!

Is this realy the generated SQL code? This looks suspicious (assignment in the WHERE) Select * FROM article `WHERE authorId=targetUserId IN` (SELECT targetUserId FROM Subscription WHERE userId=userId) — Tommos, Oct 12 '21 at 14:20

score 0 · Answer 1 · answered Oct 13 '21 at 05:11

DO NOT DO WHAT YOU ARE DOING. You are risking a SQL Injection. If you really want to do a manual query, you can do manager.query:

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

Notice the second parameter that contains parameters which is referenced by key with :userId. If you're using template strings for queries, you're probably doing something wrong.

If you want to use the QueryBuilder, then it's going to look a little different (more info on 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()

Get Article list by subscription

1 Answers1