Before the question, the post table of my current project has the following structure.

module.exports = class Post extends Model {
  static init(sequelize) {
    return super.init({      
      title: {
        type: DataTypes.TEXT,
        allowNull: false,
      },     
      desc: {
        type: DataTypes.TEXT,        
      },      
      ingredient: {
        type: DataTypes.TEXT,
        allowNull: false,
      },     
      recipes: {
        type: DataTypes.TEXT,
        allowNull: false,
      },     
      tips: {
        type: DataTypes.TEXT,        
      },     
      tags: {
        type: DataTypes.TEXT,        
      },      
    }, {
      modelName: 'Post',
      tableName: 'posts',
      charset: 'utf8mb4',
      collate: 'utf8mb4_general_ci',
      sequelize,
    });
  }
  static associate(db) {
    db.Post.belongsTo(db.User);
    db.Post.belongsToMany(db.Hashtag, { through: 'PostHashtag' });
    db.Post.hasMany(db.Comment);
    db.Post.hasMany(db.Image);
    db.Post.belongsToMany(db.User, { through: 'Like', as: 'Likers' });    
  }
};

I implemented the hashtag post search function using the following router.

router.get('/:tag', async (req, res, next) => {/hashtag/1
  try {
    const where = {};
    if (parseInt(req.query.lastId, 10)) {
      where.id = { [Op.lt]: parseInt(req.query.lastId, 10)};      
    }
    const posts = await Post.findAll({
      where,
      limit: 10,      
      order: [['createdAt', 'DESC']],      
      include: [{
        model: Hashtag,
        where: { name: decodeURIComponent(req.params.tag) },
      }, {
        model: User,
        attributes: ['id', 'nickname'],
      }, {
        model: User,
        as: 'Likers',
        attributes: ['id'],
      }, {
        model: Comment,
        include: [{
          model: User,
          attributes: ['id', 'nickname'],
        }],
      }, {
        model: Image,
      }]
    });
    res.status(200).json(posts);
  } catch (error) {
    console.error(error);
    next(error);
  }
});

The execution result was successful, and I was able to get the results I wanted.

However, we thought that it was not enough to search only hashtags, so we added the following conditions to additionally search the title and contents of the post.

router.get('/:tag', async (req, res, next) => { 
  try {
    const where = {
      title: { [Op.like]: "%"   decodeURIComponent(req.params.tag)   "%" }, // Search for the title of a post     
      recipes: { [Op.like]: "%"   decodeURIComponent(req.params.tag)   "%" }, // Search the content of the post
    };
    if (parseInt(req.query.lastId, 10)) {
      where.id = { [Op.lt]: parseInt(req.query.lastId, 10)};      
    }
    const posts = await Post.findAll({
      where,
      limit: 10,    
                      :
                      :

After that, the result of running the code did not load any posts, contrary to what I expected.

What part should be modified to implement a search function that includes the title, content, and hashtag of a post?

CodePudding user response：

I guess the problem with your function is that you are using where with AND instead of OR.

Post.findAll({
  where: {
    [Op.and]: 
   [{ title: {[Op.like]...} }, 
   { recipes: {[Op.like]...} }], // (title= %S) AND (recipes = %S)
...
)} 

// that is the same as your example

const where = {
    // Search for the title of a post     
      title: { [Op.like]: "%"..., 
    // Search the content of the post
      recipes: { [Op.like]: "%"... }, 
    };
// this is an AND operation

What you should do is use OR. More info: operators

[Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)

In your case:

Post.findAll({
  where: {
    [Op.or]: 
   [{ title: {[Op.like]...} }, 
   { recipes: {[Op.like]...} }], // (title= %S) AND (recipes = %S)
...
)} 

That is also not the proper way of searching. Maybe you should consider looking for full-text search implementations like Elastic Search or look if the database you are using has this feature built-in.