Loopback4 execute sql with params / ER_BAD_FIELD_ERROR: Unknown column 'reactionType' in 'field list'

Question

I want to increase the value of the 'reactionType' column in my table by 1 according to the 'reactionType' value I get from the api. reactionType is a variable endpoint. e.g /like /dislike ... If a like is received, I will increase the value of the like column. If there is a dislike, I will increase the value of the dislike column..

@patch('/news-reaction/{id}/{reactionType}')
  @response(204, {
    description: 'updateReaction PATCH success',
  })
  async updateReaction(
    @param.path.number('id') id: typeof  News.prototype.id,
    @param.path.string('reactionType') reactionType: string,
  ): Promise<void> {
    await this.newsReactionRepository.execute('UPDATE `news_reaction` SET `${reactionType}` =  `${reactionType}` +1 WHERE `newsId`=?', [id,reactionType]);
  }
}

I'm getting an error. how should it be?

error: Request PATCH /news-reaction/431224/sad failed with status code 500. Error: ER_BAD_FIELD_ERROR: Unknown column '${reactionType}' in 'field list'

Answer 1

I think there are two issues in your code:

1. ${} replacements do not work here
2. parameter replacements are 1-to-1 with ? in order they appear

I couldn't test it but hope this should work

await this.newsReactionRepository.execute(
  'UPDATE `news_reaction` SET `?` = `?` + 1 WHERE `newsId` = ?', [reactionType,reactionType,id]
);