javascript - Sequelize query string prefixstarts with - Stack Overflow

admin管理员组

文章数量:1355786

I'm building an autofill function that takes a string input and returns a list of string suggestions.

Sequelize's iLike:query returns every string in which the queried string appears. I would like to favour strings for which the query is a prefix. For example when query='sh' then the results should return strings that start with sh instead of having sh anywhere within the string.

This is relatively simple to do after receiving the data from the DB, however I was wondering if there is a way to acplish this via sequelize while querying the DB? If so how?

The DB size will be between 10,000 and 100,000 strings of no more than a handful of words (pany names to be exact).

Optional question: DB's usually have superior performance to generically written code, in this circumstance should there even be a noticeable difference? Or should I just collect all the data from the DB and apply some other filters on it after via vanilla JS.

let suggestions = yield db.Company.findAll({
  limit: 7,
    where: {
      pany_name: {
        $iLike: '%'+this.data.query
      }
    }
 })

I'm building an autofill function that takes a string input and returns a list of string suggestions.

Sequelize's iLike:query returns every string in which the queried string appears. I would like to favour strings for which the query is a prefix. For example when query='sh' then the results should return strings that start with sh instead of having sh anywhere within the string.

This is relatively simple to do after receiving the data from the DB, however I was wondering if there is a way to acplish this via sequelize while querying the DB? If so how?

The DB size will be between 10,000 and 100,000 strings of no more than a handful of words (pany names to be exact).

Optional question: DB's usually have superior performance to generically written code, in this circumstance should there even be a noticeable difference? Or should I just collect all the data from the DB and apply some other filters on it after via vanilla JS.

let suggestions = yield db.Company.findAll({
  limit: 7,
    where: {
      pany_name: {
        $iLike: '%'+this.data.query
      }
    }
 })

• javascript
• postgresql
• sequelize.js

Share Improve this question Follow asked Jul 12, 2016 at 18:06 Alexei DarminAlexei Darmin 2,15911 gold badge2020 silver badges3131 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 7

Seems like this is super easy! The '%' acts like a * from regex. So query + '%' returns any results where query is the prefix.

let suggestions = yield db.Company.findAll({
    limit: 5,
    where: { $or: [
      { stock_ticker: { $ilike: query + '%' } },
      { pany_name: { $ilike: query + '%' } }
    ]},
    order: '"volume" DESC'
  })

Sequelize @6.37.3

Based on @alexei-darmin's answer The code snippet below works for me

import { Op } from "sequelize";

let suggestions = yield db.Company.findAll({
    limit: 5,
    where: { $or: [
      { stock_ticker: { [Op.iLike]: query + '%' } },
      { pany_name: { [Op.iLike]: query + '%' } }
    ]},
    order: '"volume" DESC'
  })

本文标签： javascriptSequelize query string prefixstarts withStack Overflow