【Node.js】项目之搜索

搜索效果

1. 模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE '%值%'
SELECT * FROM 表名 WHERE 字段名 LIKE '值%'  -- 值开头
SELECT * FROM 表名 WHERE 字段名 LIKE '%值' -- 值结束

SELECT * FROM goods_list WHERE goods_name LIKE '%手机%'

2.搜索内容，出现响应商品，并且可以排序和分页(1随机排序 2新品 3价格排序)，每搜索一次search表对应count++

router.get('/searchbyuser', async (req, res, next) => {
  // receive parameters and search text is necessary
  let { searchtext = '', page = 1, length = 5, orderby = 1 } = req.query
  let start = (page - 1) * length
  let orderStr = '', sort = '', situation = '';
  // judge situation by orderby parameters
  switch (orderby) {
    case "1":
      orderStr = `ORDER BY rand()`
      break;
    case "2":
      situation = `AND new_status = 1`
      orderStr = `ORDER BY rand()`
      break;
    case "3":
      orderStr = `ORDER BY goods_price`
      sort = `ASC`
      break;
    default:
      orderStr = `ORDER BY rand()`
      break;
  }
  let sql = `SELECT id, goods_id, 
             third_id, goods_name, 
             CONCAT("${url}", image_url) AS image_url, 
             goods_introduce,goods_manufacturer, 
             goods_price, assem_price, new_status
             FROM goods_list WHERE goods_name LIKE '%${searchtext}%'
             ${situation} ${orderStr} ${sort}
             LIMIT ${start}, ${length}`
  let [err, result] = await db.query(sql)
  // calculate the count of product and total page, add to the result
  // query for the count of one product
  if (!err) {
    let sql1 = `SELECT COUNT(*) AS count FROM goods_list WHERE goods_name LIKE '%${searchtext}%' ${situation}`
    let [err1, result1] = await db.query(sql1)
    // add result to the data
    if (!err1) {
      let count = result1[0].count
      let totalPage = Math.ceil(count / length)
      page = Number(page)
      let data = {
        count,
        totalPage,
        page,
        result
      }
      // judge searchtext, if it exists in the search table, count++
      // else insert a new data
      let sql2 = `SELECT * FROM search WHERE search_text = '${searchtext}'`
      let [err2, result2] = await db.query(sql2)
      // console.log(result2);
      if (result2.length == 0) {
        // insert new value
        // console.log('insert new value');
        let sql3 = `INSERT INTO search(search_text, count) VALUES('${searchtext}', 1)`
        let [err3] = await db.query(sql3)
      } else {
        // console.log('update count');
        // update count
        let searchCount = result2[0].count + 1;
        // update count in search table
        let sql4 = `UPDATE search SET count = ${searchCount} WHERE search_text = '${searchtext}'`
        let [err4] = await db.query(sql4)
      }
      res.send(getMsg('Search by user success', 200, data))
    } else {
      next('Search by user failure')
    }
  } else {
    next('Search by user failure')
  }
})

返回:

{
"msg": "Search by user success",
"status": 200,
"data": {
"count": 5,
"totalPage": 1,
"page": 1,
"result": [
{
"id": 4875,
"goods_id": "1301645212010000001",
"third_id": "368",
"goods_name": "中号C型化妆刷口红收纳座掀盖防尘化妆品收纳盒",
"image_url": "http://localhost:3000/image_source/goods_loop_img/rBACVFw4QSuAFN3JAAEfjf_Vl6A950.jpg",
"goods_introduce": "创意外观、节省空间、置物方便、防尘易清洗",
"goods_manufacturer": "NITORI制造商直供",
"goods_price": 39,
"assem_price": 0,
"new_status": "1"
},
{
"id": 4976,
"goods_id": "1301645108010000001",
"third_id": "368",
"goods_name": "亚克力钻石纹护肤化妆品收纳盒口红唇膏指甲油收纳座",
"image_url": "http://localhost:3000/image_source/goods_loop_img/rBACW1qXZb-ASwGmAAFGnGfhdaw415.jpg",
"goods_introduce": "透明亚克力易查找、多格易分类收纳",
"goods_manufacturer": "NITORI制造商直供",
"goods_price": 49,
"assem_price": 0,
"new_status": "0"
},
{
"id": 5107,
"goods_id": "1301545300060000001",
"third_id": "333",
"goods_name": "水晶斜口红酒杯套装",
"image_url": "http://localhost:3000/image_source/goods_loop_img/rBACVFw1kXiAFv6sAAFcErkBovM758.jpg",
"goods_introduce": "激光冷切杯口 ，平整光滑，斜口设计，品酒更体现美感，优雅杯身，修长杯杆",
"goods_manufacturer": "希尔顿酒店产品制造商直供",
"goods_price": 89,
"assem_price": 55.63,
"new_status": "0"
},
{
"id": 4118,
"goods_id": "1301185017060100001",
"third_id": "326",
"goods_name": "柔润丰盈饱满浓郁香榭口红2支装",
"image_url": "http://localhost:3000/image_source/goods_loop_img/rBACVFkT44-ABCWVAAGNZBZs-S0101.jpg",
"goods_introduce": "轻易延展，水嫩服帖，浓郁饱满",
"goods_manufacturer": "YSL原料制造商直供",
"goods_price": 99,
"assem_price": 61.88,
"new_status": "1"
},
{
"id": 4899,
"goods_id": "1301645002020000001",
"third_id": "368",
"goods_name": "化妆品收纳盒 透明梳妆台桌面口红护肤品收纳盒2件套",
"image_url": "http://localhost:3000/image_source/goods_loop_img/rBACYVpXG7WART6TAAFxOaQMsvI307.jpg",
"goods_introduce": "叠加组合大容量，收纳男、女清洁护肤品、化妆品及配饰",
"goods_manufacturer": "NITORI制造商直供",
"goods_price": 139,
"assem_price": 0,
"new_status": "1"
}
]
}
}

3.热门搜索

// hot search
router.get('/hotsearch', async (req, res, next) => {
    let { limit = 9 } = req.query
    let sql = `SELECT * FROM search ORDER BY count DESC LIMIT ${limit}`
    let [err, result] = await db.query(sql)

    if (!err) {
        res.send(getMsg('Hot search success', 200, result))
    } else {
        next('Hot search failure')
    }
})

返回：

{
"msg": "Hot search success",
"status": 200,
"data": [
{
"id": 6,
"search_text": "休闲",
"count": 282
},
{
"id": 7,
"search_text": "电脑",
"count": 154
},
{
"id": 5,
"search_text": "秋衣",
"count": 16
},
{
"id": 15,
"search_text": "背心",
"count": 11
},
{
"id": 1,
"search_text": "裤子",
"count": 8
},
{
"id": 3,
"search_text": "冰箱",
"count": 8
},
{
"id": 8,
"search_text": "口红",
"count": 7
},
{
"id": 14,
"search_text": "咖啡",
"count": 7
},
{
"id": 2,
"search_text": "大衣",
"count": 6
}
]
}