I have this data on database , I want that if user search the "ja" the result will display both "james" and "jam"

  async getUsername(searchname){
    let result = await Database.getConnection().models.User.findAll({ 
      where: { 
        FirstName: searchname
      },
      raw: true
    });
    
    return result;
  },

data

{
  Firstname: "james",
  ....,
},
{
  Firstname: "jam",
  ....,
},
{
  Firstname: "Harltan",
  ....,
},
{
  Firstname: "Hames",
  ....,
}

the current result is either i type or search the ja, no result found, but if i search jam the "jam" data is display

the db i use is mysql

CodePudding user response：

Assuming you're using Sequelize, you can simply use the startsWith operator

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

// ...

let result = await Database.getConnection().models.User.findAll({ 
  where: { 
    FirstName: {
      [Op.startsWith]: searchname
    },
  },
  raw: true,
});

See the Operators documentation for more information.

CodePudding user response：

The range of search operators available in Sequelize v6 are

      [Op.like]: '%hat',                       // LIKE '%hat'
      [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
      [Op.startsWith]: 'hat',                  // LIKE 'hat%'
      [Op.endsWith]: 'hat',                    // LIKE '%hat'
      [Op.substring]: 'hat',                   // LIKE '%hat%'
      [Op.iLike]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
      [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
      [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
      [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (PG only)
      [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (PG only)

The search for strings starting with your search as your example

const { Op } = require("sequelize")

let result = await Database.getConnection().models.User.findAll({ 
  where: { 
    FirstName: {
      [Op.startsWith]: searchname,
    }
  },
  raw: true
});