How to find user first name letter

How can I find user with first letter in their name like for example my name is “Nathan” and when I type “n” in search input it will show user start with an “n” but not user that not start with letter “n” but contain letter “n” like Henry, Connor..

here is my searchController.js:

exports.searchAll = async (req, res) => {
  // Grab query
  const query = req.params.q;

  // Search for user's
  const usersFound = await models.User.findAll({
    where: {
      [Op.or]: [
        {
          fullname: {
            [Op.iLike]: "%" + query + "%",
          },
          // Only include full account users
          passwordhash: {
            [Op.ne]: null, // full account users have a password
          },
          verifiedDT: { [Op.ne]: null },
        },
        {
          institution: {
            [Op.iLike]: "%" + query + "%",
          },
          // Only include full account users
          passwordhash: {
            [Op.ne]: null, // full account users have a password
          },
          verifiedDT: { [Op.ne]: null },
        },
      ],
    },
    attributes: [
      "fullname",
      "public_user_id",
      "institution",
      "location",
      "webpage",
      "linkedin",
      "major",
      "bio",
      "picture",
      "id",
    ],
    include: [
      {
        model: models.Rating,
        attributes: ["skillset_rating", "team_member_rating"],
      },
      {
        model: models.Skill,
        attributes: ["skill"],
      },
    ],
  });

  // Search for teams
  const teamsFound = await models.Team.findAll({
    where: {
      title: {
        [Op.iLike]: "%" + query + "%",
      },
      status: {
        [Op.ne]: "Closed", // past teams
      },
      creatorId: {
        [Op.ne]: null,
      },
    },
    attributes: ["public_team_id", "title", "mission", "status"],
    include: [
      {
        // Creators name
        model: models.User,
        attributes: ["fullname"],
      },
    ],
  });

  // Run searches
  const searchData = await Promise.all([usersFound, teamsFound]);
  res.status(200).json(searchData);
};

and here is my model user.js:

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
        allowNull: false,
      },
      fullname: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      passwordhash: DataTypes.STRING,
      institution: DataTypes.STRING,
      bio: DataTypes.STRING,
      creator_user_id: DataTypes.UUID,
      public_user_id: DataTypes.STRING,
      picture: DataTypes.STRING(300),
      email: { type: DataTypes.STRING, unique: true },
      gender: DataTypes.STRING,
    },
    {
      tableName: "Users",
      timestamps: true,
      indexes: [
        {
          unique: false,
          fields: ["email", "id", "fullname", "public_user_id"],
        },
      ],
    }
  );

Answer

Your query is currently looking for fullname ILIKE '%n%', which means any combination of characters before or after the letter n. If you want to get only results that begin with the letter n, remove the first % wildcard.

Advertisement

Answer