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:

Anyone knows a solution? Thanks in advance!

This is not possible without #3095 - Untill that is implemented, sequelize thinks everything in where is on the main table — Jan Aagaard Meier
– Jan Aagaard Meier, Commented Jul 8, 2015 at 8:22
Thanks @JanAagaardMeier. Hmm. is there no alternative solution? Like make some parts of the code above as raw queries? Or using a full raw query is the only solution for now? — John Kenn
– John Kenn, Commented Jul 13, 2015 at 20:11

igneosaur · Accepted Answer · 2016-05-19 14:37:20Z

19

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().

edited May 19, 2016 at 14:37

answered May 19, 2016 at 14:32

igneosaur

3,3663 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ivan Rubinson Over a year ago

This can be refactored to use [Sequelize.Op.substring]

Anton Over a year ago

and depends on the DB server, probably {[Op.iLike]: `%${i}%`} (for Postgres) would be better choice to have case insensitive search

ahmadalibaloch · Accepted Answer · 2020-02-11 14:50:18Z

6

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.

edited Feb 11, 2020 at 14:50

answered Feb 11, 2020 at 11:01

ahmadalibaloch

6,0413 gold badges53 silver badges61 bronze badges

Collectives™ on Stack Overflow

How to implement search feature using SequelizeJS?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related