/**
 * Search route
 */

const { Router } = require('express');
const joi = require('joi');
const { searchSchema } = require('../../schema');

function getQuery(db, request, uid) {
    const { table, column, searchTerm, numResults } = request;

    const searchTermEscaped = searchTerm.replace(/[^\w]+/g, '');

    const query = qb => qb.select(column, db.raw(`SUM(IF(??.?? LIKE '${searchTermEscaped}%', 1, 0)) AS matches`, [table, column]))
        .from(table)
        .where(`${table}.${column}`, 'like', `%${searchTerm}%`)
        .andWhere(`${table}.${column}`, 'not like', searchTerm)
        .andWhere(`${table}.uid`, '=', uid)
        .groupBy(`${table}.${column}`)
        .orderBy('matches', 'desc')
        .orderBy(column)
        .limit(numResults)
        .as('items');

    if (['food', 'general'].includes(table) && column === 'item') {
        return db.select('items.item', db.raw('COALESCE(nextValues.category, \'\') as nextCategory'))
            .from(query)
            .leftJoin(`${table} as nextValues`, 'nextValues.id', qb => qb.select('id')
                .from(table)
                .where('item', '=', db.raw('items.item'))
                .andWhere('uid', '=', uid)
                .limit(1)
            );
    }

    return query(db);
}

/**
 * @swagger
 * /data/search/{dataType}/{column}/{searchTerm}/{numResults}:
 *     get:
 *         summary: Search suggestions
 *         tags:
 *         - Search
 *         operationId: search
 *         description: |
 *             Get search suggestions for data input
 *         produces:
 *         - application/json
 *         parameters:
 *         - in: path
 *           name: dataType
 *           description: The type of data to get
 *           required: true
 *           type: string
 *         - in: path
 *           name: column
 *           description: The column of data to search
 *           required: true
 *           type: string
 *         - in: path
 *           name: searchTerm
 *           description: The term to search for
 *           required: true
 *           type: string
 *         - in: path
 *           name: numResults
 *           description: The number of results to return
 *           required: false
 *           type: number
 *         responses:
 *             200:
 *                 description: successful operation
 *                 schema:
 *                     type: object
 *                     properties:
 *                         error:
 *                             type: boolean
 *                             example: false
 *                         data:
 *                             type: object
 *                             properties:
 *                                 list:
 *                                     type: array
 *                                     items:
 *                                         type: string
 */
function routeGet(config, db) {
    return async (req, res) => {
        const { error, value } = joi.validate(req.params, searchSchema);

        if (error) {
            return res.status(400)
                .json({ errorMessage: error.message });
        }

        try {
            const result = await getQuery(db, value, req.user.uid);

            const data = {
                list: result.map(({ [value.column]: item }) => String(item))
            };

            if (result.length && result[0].nextCategory) {
                const nextCategory = result.map(({ nextCategory: item }) => String(item));

                return res.json({ data: { ...data, nextCategory } });
            }

            return res.json({ data });
        }
        catch (err) {
            return res.status(500)
                .json({ errorMessage: err.message });
        }
    };
}

function handler(config, db) {
    const router = new Router();

    router.get('/:table/:column/:searchTerm/:numResults?', routeGet(config, db));

    return router;
}

module.exports = {
    routeGet,
    handler
};


@@ -252,38 +252,53 @@
 nst 
-searchTermEscaped = searchTerm
+tsQuery = searchTerm%0A        .trim()%0A        
 .rep
@@ -311,149 +311,141 @@
 %5B%5E%5Cw
+%5Cs+
 %5D
-+
 /g, '')
-;%0A
 %0A    
-const query = qb =%3E qb.select(column, db.raw(%60SUM(IF(??.?? LIKE '$%7BsearchTermEscaped%7D%25', 1, 0)) AS matches%60, %5Btable, 
+    .split(/%5Cs+/)%0A        .map(word =%3E %60$%7Bword%7D:*%60)%0A        .join(' %7C ');%0A%0A    const query = qb =%3E qb.distinct(
 column
-%5D)
 )%0A  
@@ -481,85 +481,34 @@
 here
-(%60$%7Btable%7D.$%7Bcolumn%7D%60, 'like', %60%25$%7BsearchTerm%7D%25%60)%0A        .andWhere(%60
+Raw(%60to_tsvector(%22
 $%7Btable%7D
 .$%7Bc
@@ -495,33 +495,35 @@
 vector(%22$%7Btable%7D
-.
+%22.%22
 $%7Bcolumn%7D%60, 'not
@@ -519,33 +519,46 @@
 umn%7D
-%60, 'not like', searchTerm
+%22) @@ to_tsquery('$%7BtsQuery%7D') = true%60
 )%0A  
@@ -620,64 +620,14 @@
 pBy(
-%60$%7Btable%7D.$%7Bcolumn%7D%60)%0A        .orderBy('matches', 'desc'
+column
 )%0A  
@@ -792,16 +792,29 @@
 .select(
+%0A            
 'items.i
@@ -818,16 +818,28 @@
 s.item',
+%0A           
  db.raw(
@@ -848,25 +848,26 @@
 OALESCE(next
-V
+_v
 alues.catego
@@ -879,25 +879,26 @@
 '%5C') as next
-C
+_c
 ategory'))%0A 
@@ -894,16 +894,25 @@
 tegory')
+%0A        
 )%0A      
@@ -965,25 +965,26 @@
 ble%7D as next
-V
+_v
 alues%60, 'nex
@@ -988,9 +988,10 @@
 next
-V
+_v
 alue

Use postgresql full text matching for search endpoint