Boost Database by AI Search: No results even when AI Search finds something (#3556572) · Issues · project / ai_search

Boost Database by AI Search: No results even when AI Search finds something

>>> [!note] Migrated issue   Reported by: [tim.steinberg](https://www.drupal.org/user/3727263) >>> <h3 id="summary-problem-motivation">Problem/Motivation</h3> First of all, I'm not sure if this is a bug or if this is intended to be this way. I have set up a Database Search which uses Milvus to boost the results with an AI Search. The Database Search is simple and uses only two fields. I want to use the Database Search only for exact matches (like SKU codes), since the AI Search does not perform well for this type of search. I want an entity node (which is found by the AI Search) to always be included in the results, regardless of whether it matches the word patterns used by the Database Search. However, currently I do not get it back unless it is present in the filtered data returned by the Database Search. I investigated the code and extracted this SQL query, which seems to be the cause of the issue: <pre>SELECT COUNT(*) AS "expression" FROM (     SELECT         t.item_id AS "item_id",         SUM(t.score) AS "score",         1 AS "expression",         CASE             WHEN t.item_id = 'entity:node/9909:de' THEN 1             ELSE 0         END AS "ai_boost"     FROM (         SELECT             t.item_id AS "item_id",             SUM(t.score) AS "score",             CASE WHEN t.word LIKE '%runde 40%' THEN 1 ELSE 0 END AS "w1",             CASE WHEN t.word LIKE '%40 mm%' THEN 1 ELSE 0 END AS "w2",             CASE WHEN t.word LIKE '%mm etiketten%' THEN 1 ELSE 0 END AS "w3"         FROM             search_api_db_pages_search_text_5 t         WHERE             (                 t.word LIKE '%runde 40%' ESCAPE '\\'                 OR t.word LIKE '%40 mm%' ESCAPE '\\'                 OR t.word LIKE '%mm etiketten%' ESCAPE '\\'             )             AND t.field_name IN ('sku', 'software_code')         GROUP BY             t.item_id,             w1,             w2,             w3     ) t     LEFT OUTER JOIN search_api_db_pages_search t_2         ON t.item_id = t_2.item_id     WHERE         t_2.search_api_language IN ('de', 'und')     GROUP BY         t.item_id     HAVING         (SUM(w1) >= 1)         OR (SUM(w2) >= 1)         OR (SUM(w3) >= 1)         OR (t.item_id IN ('entity:node/9909:de'))     ORDER BY         ai_boost DESC ) subquery;</pre>You can see that the node found by AI (entity:node/9909:de) is intended to be included in the final results. However, because the innermost subquery only selects item_ids that match the WHERE clause: <pre>WHERE (   t.word LIKE '%runde 40%'   OR t.word LIKE '%40 mm%'   OR t.word LIKE '%mm etiketten%' ) AND (t.field_name IN ('sku', 'software_code'))</pre> ...the outer aggregations will never see entity:node/9909:de unless it is present in the filtered data, so it does not get included in the final result as I would expect it. <h3 id="summary-proposed-resolution">Proposed resolution</h3> Update the SQL query logic so that database search results always include nodes found by the AI search, even if they do not match the current word patterns or filters.

issue