如何使用 SequelizeJS 实现搜索功能? [英] How to implement search feature using SequelizeJS?

问题描述

我有一个 Ticket 模型和一个 Comment 模型.Ticket 与 Comment 模型有 hasMany 关系.我想通过关键字搜索门票.关键字将与工单模型的主题属性和评论模型的正文属性进行匹配.

I have a Ticket model and a Comment model. The Ticket has a hasMany relationship to Comment model. I want to search tickets by a keyword. The keyword will be matched againts the subject attribute of the ticket model and the body attribute of the comment model.

下面的代码不起作用:

var options = {
  where: {
    $or: [
      {
        subject: {
          like: '%' + query + '%'
        },
      },
      {
        'Comment.body': {
          like: '%' + query + '%'
        },
      }
    ]
  },
  include: [
    { model: Comment },
  ]
};

Ticket.findAll(options);

这是错误:可能未处理的 SequelizeDatabaseError:列 Ticket.Comment.body 不存在"

This is the error: "Possibly unhandled SequelizeDatabaseError: column Ticket.Comment.body does not exist"

我也尝试了下面的代码，但它也不起作用:

I also tried the code below but it also doesn't work:

var options = {
  where: {
    CompanyId: req.company.id,
    $or: [
      {
        subject: {
          like: '%' + query + '%'
        },
      },
      sequelize.cast(sequelize.col('comment.body'), 'TEXT', 'LIKE', '%' + query + '%')
    ]
  },
  include: [
    { model: Comment, as: 'comment', where: {} },
  ]
};

Ticket.findAll(options);

错误是:可能未处理的错误:评论(comment)与票证无关！"

The error is: "Possibly unhandled Error: Comment (comment) is not associated to Ticket!"

还有这个:

var options = {
  where: {
    CompanyId: req.company.id,
    $or: [
      {
        subject: {
          like: '%' + query + '%'
        },
      },
      sequelize.where(sequelize.col('Comments.body'), 'LIKE', '%' + query + '%')
    ]
  },
  include: [
    { model: Comment},
  ]
};

Ticket.findAll(options);

错误:可能未处理的 SequelizeDatabaseError:缺少表评论"的 FROM 子句条目"

Error: "Possibly unhandled SequelizeDatabaseError: missing FROM-clause entry for table "Comments""

我使用的是 SequelizeJS 2.0.4 版

I'm using SequelizeJS version 2.0.4

我在 Github 上的 Sequelizejs 存储库中看到了这些相关问题:

I saw these related issues on the Sequelizejs repository on Github:

• https://github.com/sequelize/sequelize/issues/3261
• https://github.com/sequelize/sequelize/issues/3095
• https://github.com/sequelize/sequelize/issues/3527

有人知道解决办法吗?提前致谢！

Anyone knows a solution? Thanks in advance!

推荐答案

对你来说可能有点太晚了，但对其他人来说；#3095 更新了位一个解决方案:

Probably a bit too late for you, but for anyone else; #3095 was updated with a bit of a solution:

var options = {
  where: {
    $or: [
      { 'subject': { like: '%' + query + '%' } },
      { '$Comment.body$': { like: '%' + query + '%' } }
    ]
  },
  include: [{ model: Comment }]
};

诀窍在于那些美元符号，尽管仍然存在问题 在设置 limit 或使用 findAndCountAll() 查询时使用此解决方案.

The trick is in those dollar signs, though there are still problems with this solution when limit is set or when querying with findAndCountAll().

这篇关于如何使用 SequelizeJS 实现搜索功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！