mysqlnlpmatch-againstagainst

Use results of a mySQL SELECT as input of a MATCH AGAINST natural language query


I need to make a research by natural language on a mySQL table field taking as input the values of another table field. I tried something similar but, as I suspected, it was not correct:

    SELECT id, name, MATCH(name), 
    AGAINST 
      (
        SELECT name
        FROM table2
      ) AS score
    FROM table1
    WHERE MATCH(name), 
    AGAINST 
      (
        SELECT name
        FROM table2
      )

Any idea?

UPDATE 1

I followed the kind example here below but i got the error "#1064 - SQL query syntax error near 'tb2". I cannot see this syntax error. Here the code I am testing:

SELECT name, MATCH(name) AGAINST 
(

    (SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) tb2 

) AS score
FROM 
(
    
    SELECT alias_name as name
    FROM temp_active_ingredients_aliases
    


) 
WHERE MATCH(name) AGAINST 
(

    (SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tb3
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name) tb4

)

The inner queries return the list of active_ingredients.name that do not exactly match at least one of the temp_active_ingredients_alieases.alias_name fields. So that I then try a match of the not exactly matching name(s) with the alias_name(s) by natural language FULLTEXT research. To be noted that the following inner queries are working properly:

    SELECT name
    FROM 
    (
        SELECT name 
            FROM active_ingredients

        UNION ALL
        
        SELECT active_ingredients.name as name
        FROM active_ingredients
        INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

    ) tbl
    GROUP BY name
    HAVING count(*) = 1
    ORDER BY name

I am quite sure that the syntax error is very stupid, but i cannot see it.

UPDATE 2

Here the links to the code for generating the two tables (schema and some data)


Solution

  • Yiu were almost there

    To explain a bit further.

    1. You put after the match(name) a comma that is wrong at that position.
    2. GROUP_CONCAT is there because MySQL expects there a list of words and this was the easiest way to achieve that, for further information about FulltextaSearch and optimization see the manual As you see in the example a space as separator doesn't change anything
    3. IN NATURAL LANGUAGE MODE was only a assumption of mine as you wrote research by natural language all other options a also in that link explained
    CREATE TABLE table1
    (id int,
    name text,
    FULLTEXT(name))
    
    INSERT INTo table1 VALUES(1,'text1')
    
    CREATE TABLE table2
    (id int,
    name text)
    
    INSERT INTO table2 VALUES(1,'text1'),(2,'text2')
    
    SELECT t1.id, t1.name, MATCH(t1.name) 
    AGAINST 
      (
        (SELECT GROUP_CONCAT(name)
        FROM table2) IN NATURAL LANGUAGE MODE
      ) AS score
    FROM table1 t1 
    WHERE MATCH(t1.name) 
    AGAINST 
      (
        (SELECT GROUP_CONCAT(name)
        FROM table2) IN NATURAL LANGUAGE MODE
      )
      
    
    id | name  |                      score
    -: | :---- | -------------------------:
     1 | text1 | 0.000000001885928302414186
    
    SELECT t1.id, t1.name, MATCH(t1.name) 
    AGAINST 
      (
        (SELECT GROUP_CONCAT(name SEPARATOR ' ')
        FROM table2) IN NATURAL LANGUAGE MODE
      ) AS score
    FROM table1 t1 
    WHERE MATCH(t1.name) 
    AGAINST 
      (
        (SELECT GROUP_CONCAT(name SEPARATOR ' ')
        FROM table2) IN NATURAL LANGUAGE MODE
      )
      
    
    id | name  |                      score
    -: | :---- | -------------------------:
     1 | text1 | 0.000000001885928302414186
    

    db<>fiddle here

    I corrected your mistakes

    SELECT 
        name,
        MATCH (name) AGAINST ((SELECT 
                name
            FROM
                (SELECT 
                    name
                FROM
                    active_ingredients UNION ALL SELECT 
                    active_ingredients.name AS name
                FROM
                    active_ingredients
                INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name) tbl
            GROUP BY name
            HAVING COUNT(*) = 1
            ORDER BY name) ) AS score
    FROM
        (SELECT 
            alias_name AS name
        FROM
            temp_active_ingredients_aliases) db2
    WHERE
        MATCH (name) AGAINST ((SELECT 
                name
            FROM
                (SELECT 
                    name
                FROM
                    active_ingredients UNION ALL SELECT 
                    active_ingredients.name AS name
                FROM
                    active_ingredients
                INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name) tb3
            GROUP BY name
            HAVING COUNT(*) = 1
            ORDER BY name) )
    

    Without proper datqa, i can not test it, in can only remove syntax error

    SELECT 
        name,
        MATCH (name) AGAINST ((    SELECT name
        FROM 
        (
            SELECT name 
                FROM active_ingredients
    
            UNION ALL
            
            SELECT active_ingredients.name as name
            FROM active_ingredients
            INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name
    
        ) tbl
        GROUP BY name
        HAVING count(*) = 1
        ORDER BY name) ) AS score
    FROM
        (SELECT 
            alias_name AS name
        FROM
            temp_active_ingredients_aliases) db2
    WHERE
        MATCH (name) AGAINST ((    SELECT name
        FROM 
        (
            SELECT name 
                FROM active_ingredients
    
            UNION ALL
            
            SELECT active_ingredients.name as name
            FROM active_ingredients
            INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name
    
        ) tbl
        GROUP BY name
        HAVING count(*) = 1
        ORDER BY name) )