MySQL FULLTEXT returning only best answer

Question

I'm trying to modify the code for fullpath retrieval from this post: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ in nested-set model to use FULLTEXT instead of exact match.

Here's what I want: I want to obtain best match for searched from MATCH() AGAINST() and than use '=' sign to find only this one result.

Here's what I've tried:

DELIMITER //
DROP PROCEDURE IF EXISTS find_node;
//

CREATE DEFINER = CURRENT_USER PROCEDURE find_node (
        IN searched TEXT
)

proc: BEGIN

    SELECT DISTINCT parent.content
    FROM tree AS node,
             tree AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.content = (SELECT content FROM tree WHERE MATCH(node.content) AGAINST(searched IN BOOLEAN MODE) LIMIT 1)
    ORDER BY parent.lft;

END //
DELIMITER ;

Line beggining with AND node.content, but it seems that it's not returning any results after running the procedure. I would like to be able to obtain second, third and n best match as well (maybe using LIMIT 1 OFFSET n, where n is a result number?).

Thanks for your help

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

This isn't logically sensible. Take a look at your subquery again.

SELECT content 
  FROM tree /* << tree? */ 
 WHERE MATCH(node.content) /* << node? */ ...

Your table here is tree. Specifying a match against a column in node applies no sensible selection criteria to tree but is going to match every row in tree, just as if you had said WHERE 1 or WHERE 1 = 1 or WHERE 2+2 != 5, or no rows in tree, as would WHERE 1 = 0. The server returns all rows, LIMIT 1, and if that 1 row doesn't provide the correct 'content,' then the outer query returns no rows... or the subquery returns no rows because the WHERE is imposssible to satisfy. It's not clear, which, without further analysis, but as written, the query does not seem to do what you intend.

I believe you intended this:

 WHERE MATCH(tree.content) AGAINST ...

I admit I am not fully convinced of the validity of the approach, as a solution to what you're really trying to accomplish, but this seems like a logical error that would explan "no results."

You might also find value in my answer to MYSQL matching one column to another in a different table via a FULLTEXT index on dba.SE, the Stack Exchange site for database administrators. Of course, by "a different table," the question actually referred to a join, so that solution is also useful for joining a table to itself. The solution uses a stored function to relieve the restriction against referencing a column in AGAINST(), which you don't need, but it also handles the LIMIT 1 so that you get a scalar value.

However... it seems like if you want multiple rows, a scalar subquery or a stored function isn't right, either. You need a derived table, which MySQL calls a subquery in the FROM clause. Support for LIMIT in derived tables varies by server version, IIRC.

MySQL FULLTEXT returning only best answer

1 Answers1