How to join tables without losing the FULLTEXT-Index?

Question

I'm currently trying to write a little multilingual searchengine for my website.

I wrote a function to get the text in the requested language and fall back to english, if the text doesn't exist in that language.

But since I get an error, when calling the function inside the MATCH clause and I don't want to have to call it 3 times, I just wrote the function code as an INNER JOIN, but now I lose my FULLTEXT-index.

Questions:

• How can I maintain my FULLTEXT-index?
• Is there a better way than joining the second table?
• Any other mistakes?

Error:

Can't find fulltext index matching the column list

TABLE "translations":

• TYPE------NAME---- INDEX
• INT---------"ID"------- unique (primary)
• INT---------"tr_num"-
• TEXT"-----"text"----- FULLTEXT
• CHAR(2)-"lang"-----

Searchengine: (shortened)

SELECT
    c.ID,
    c.name,
    tr.description,
    MATCH(c.name) AGAINST(?) AS relevance_title,
    MATCH(tr.description) AGAINST(?) AS relevance_description,
FROM
    companies c
    #Joining transations (function code)
    INNER JOIN (
        SELECT
            tr1.tr_num,
            #Select translation if available
            COALESCE(tr2.text, tr1.text) AS description
        FROM
            translations tr1
            LEFT JOIN
                translations tr2
            ON
                tr1.tr_num = tr2.tr_num
            AND
                tr2.lang = '$lang' #PHP - preferred langauge
        WHERE
            tr1.lang = 'en' #default language (english)
    ) tr
      ON
        tr.tr_num = c.description_tr_num
WHERE
    MATCH(c.name) AGAINST(?)
    OR MATCH(tr.description) AGAINST(?)
ORDER BY
    ((relevance_title * "/*PHP*/.$config['relevance_title'].") + (relevance_description * "/*PHP*/.$config['relevance_description'].")) DESC

Function: (unimportant - just incase I could use it in my search)

BEGIN
    DECLARE output TEXT;

    SELECT
        COALESCE(tr2.text, tr1.text)
    INTO
        output
    FROM
        translations tr1
        LEFT JOIN
            translations tr2
        ON
            tr1.tr_num = tr2.tr_num
        AND
            tr2.lang = input_lang
    WHERE
        tr1.tr_num = input_tr_num
    AND
        tr1.lang = 'en';

    RETURN output;
END

Thanks in advance! -Minding

Answer 1

Here is how I solved the problem, just in case somebody has a similar problem:

Take another approach, instead of joining first, then searching I chose to search first, then to join:

  #INSTEAD OF
COALESCE(tr2.text, tr1.text) AS description
  #AND
MATCH(description) AGAINST(?) AS relevance_description

  #USE
GREATEST(
    MATCH(tr2.text) AGAINST(?),
    MATCH(tr1.text) AGAINST(?)
) AS relevance_description