如何在 SQLITE 中进行多词部分搜索? [英] How to do a multi word partial search in SQLITE?

问题描述

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

Hello i want to write a multi word partial search in SQLITE for example if the user types 'red' i will give all antibodies with red in their names or in their colors or reactivity and if the user types 'red 20' i want to give the intersection of antibodies with red and 20 in their name or their colors or reactivity. I already wrote this but i think there should be something in SQL for making it easier.

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;
            }
          }
        });
    });
  });
}

推荐答案

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

Create a CTE for the values that you want to search for, like 'red' and '20' and another CTE that returns the columns id and name of all 3 tables.
Join the tables, group by id and set the condition in the HAVING clause:

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)

这篇关于如何在 SQLITE 中进行多词部分搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！