How to do a multi word partial search in SQLITE?

Question

Hello i want to write a multi word partial search in SQLITE:

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

Answer 1

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)