I’m new to PostgreSQL and Sequelize. I need to implement a global search in my user table. Users are linked with userAddress, userEducation, and userProfession tables using a hasMany relation. I want to perform a full-text search using Sequelize and PostgreSQL that searches in the user table and all joined tables. The search must support starting with, ending with, and middle substring searches.
const _searchQuery = query.replace(/[^a-zA-Z0-9s]/g, ''); const formattedSearchQuery =
${_searchQuery}:* | ${_searchQuery}:* | ${_searchQuery}:*`;
const result = await User.findAll({
where: literal(search_vector @@ to_tsquery('english', :searchQuery)
),
replacements: { searchQuery: formattedSearchQuery },
raw: true
});`
This code snippet effectively handles searching for prefixes. For example, if the name is ‘John’ and we search for ‘joh’ or ‘jo,’ it functions correctly. However, it doesn’t work for suffix searches like ‘ohn’ or ‘oh.
help me to implement this correctly
Nayan More is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.