sequelize fulltext query using parameters

Question

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

I receive 'order by', 'order (desc/asc)', 'limit', 'offset' from the frontend
I also need to search the record using match...against. 'like' is too slow for searching.
There's a mapped model with this query.

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!

astangelo · Accepted Answer · 2020-08-26 19:53:04Z

2

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

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

answered Aug 26, 2020 at 19:53

astangelo

6578 silver badges19 bronze badges

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

1 Comment

J_Mok Over a year ago

Thanks astangelo! that helped me a lot :)

Collectives™ on Stack Overflow

sequelize fulltext query using parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related