How to implement search feature using SequelizeJS?

Question

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.

The code below doesn't work:

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

Ticket.findAll(options);

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);

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

And this one:

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);

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

I'm using SequelizeJS version 2.0.4

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!

Answer 1

const { Op } = require("sequelize");

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

Op.iLike can be used for case-insensitive search.

Answer 2

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 }]
};

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