时间戳

首页 > 解决方案 > 如何在 SQLITE 中进行多词部分搜索？

sql - 如何在 SQLITE 中进行多词部分搜索？

问题描述

您好，我想在 SQLITE 中编写一个多词部分搜索，例如，如果用户键入“红色”，我将给出名称或颜色或反应性中带有红色的所有抗体，并且如果用户键入“红色 20”，我想给出名称或颜色或反应性中带有红色和 20 的抗体的交集。我已经写了这个，但我认为 SQL 中应该有一些东西可以让它更容易。

const searchMultiWord = (
  index: number,
  amount: number,
  information: string[],
  startDate: number,
  endDate: number,
) => {
  return new Promise<Antibodies[]>((resolve, reject) => {
    let antibodies: Antibodies[] = [];
    let totalCount: number;
    let defaultSql = `SELECT id, name as antibodyName 
                          FROM Antibodies 
                          WHERE id IN (
                            SELECT id FROM
                            (
                              SELECT id FROM Antibodies WHERE name LIKE ?
                              UNION all
                              SELECT antiId FROM AssignedColors WHERE name LIKE ?
                              UNION all
                              SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
                            )`;
    let defaultParams = [`${startDate}`, `${endDate}`, `${amount}`, `${index}`]
    for (let i = 0; i < information.length - 1; i++) {
      defaultSql += `INTERSECT
      SELECT id FROM
      (
        SELECT id FROM Antibodies WHERE name LIKE ?
        UNION all
        SELECT antiId FROM AssignedColors WHERE name LIKE ?
        UNION all
        SELECT antiId FROM AssignedReactivities WHERE name LIKE ?
      )`;
      defaultParams.unshift(`%${information[i]}%`, `%${information[i]}%`, `%${information[i]}%`);
    }
    defaultParams.unshift(`%${information[information.length - 1]}%`, `%${information[information.length - 1]}%`,
      `%${information[information.length - 1]}%`);
    defaultSql += `) AND dateOfCreation >= ? AND dateOfCreation <= ?
    ORDER BY dateOfCreation DESC LIMIT ? OFFSET?;`;
    db.serialize(() => {
      db.each(defaultSql,
        defaultParams
        , (err, antibody) => {
          if (err) {
            return err.message;
          } else {
            db.all('SELECT name, locations, colorId FROM AssignedColors WHERE antiId = ?', [antibody.id], (err, colors) => {
              if (err) {
                reject(err.message)
              } else {
                antibody.colors = colors;
                antibodies.push(antibody);
                if (totalCount === antibodies.length) {
                  resolve(antibodies);
                }
              }
            });
          }
        }, (err, count) => {
          if (err) {
            reject(err.message)
          } else {
            if (count === 0) {
              resolve(antibodies);
            } else {
              totalCount = count;
            }
          }
        });
    });
  });
}

标签： sqltypescriptsqlitenode-sqlite3

解决方案

CTE为您要搜索的值创建一个，例如'red'和'20'另一个CTE返回所有 3 个表的列id和name。
加入表，group by id并在HAVING子句中设置条件：

WITH 
  search(val) AS (VALUES ('red'), ('20')), 
  cte AS (
    SELECT id, name FROM Antibodies
    UNION ALL
    SELECT antiId, name FROM AssignedColors
    UNION ALL
    SELECT antiId, name FROM AssignedReactivities
  )
SELECT c.id
FROM cte c INNER JOIN search s
ON c.name LIKE '%' || s.val || '%'  
GROUP BY c.id
HAVING COUNT(DISTINCT s.val) = (SELECT COUNT(*) FROM search)

推荐阅读