CODEWITHSUNDEEP
mysqlnode.jsexpresssequelize.jsfull-text-search
J_Mok
J_Mok

Reputation: 45

sequelize fulltext query using parameters

Hi I'm currently trying to query records from db and these are the conditions

so I tried

    let order_by = req.query.orderby;
    let order = req.query.order;
    let page = req.query.pagenum;
    let perpage = req.query.parpage;
    let searchword = req.query.foodsearch;
    let offset = (parseInt(page) - 1) * parpage;

    let foods = await models.food.findAll({
        limit: parseInt(perpage),
        offset: offset,
        order: [
            [order_by, order]
        ],

    //  where: Sequelize.literal
    //  (
    //      `MATCH 
    //      (Name, Place, RestoNum, Ingredient, ChefName, Region...) 
    //      AGAINST
    //      ( ? IN NATURAL LANGUAGE MODE)`, 
    //      { replacements: [ searchword ] }
    //  )
    });

but the commented part seems wrong in this code. I tried the raw query, but then I can't parameterize those order by, order, offset, limit variables. I don't want to just add them like ${orderby} because it's risky.

Please let me know if you have any solution for this issue.

Thank you in advance!

Upvotes: 0

Views: 2187

Answers (1)

astangelo
astangelo

Reputation: 627

You're confusing the Sequelize.literal() and sequelizeInstance.query() APIs.

  1. .literal() only take a string. If you want to use the object notation for your query, your commented code will work. Except that there is no second argument. You will need to concatenate-in or interpolate-in your search term into the AGAINST clause. Also, don't forget your quotes. The output of the literal() is essentially a string. Your MySQL FTS parameter will need the correct type of quotes around it, just as they would appear in your raw SQL query.
  2. .query() DOES take an options parameter. Through this, you don't have to use string interpolation, you can use named replacements or bound-parameters. This will not only allow you to place in your searchword parameter, but whatever ORDER BY clause you want, as well.

I would go with Option 1. That's what we are doing for our FTS, in MS SQL.

Upvotes: 2

Related Questions