It is possible to use MATCH AGAINST with COALESCE?

Question

It is possible to match COALESCE(x,y) from two different tables against a string?

Here is my request (not working...)

SELECT COALESCE(title_translations.title,collection.title) 
LEFT JOIN title_translations ON title_translations.ref_collection=collection.id
WHERE MATCH(COALESCE(title_translations.title,collection.title)) AGAINST("string")

The request works properly if i try to only match collection.title, but doesn't with both

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html says:

MATCH() takes a comma-separated list that names the columns to be searched.

By trying to use COALESCE(), you're passing a string expression to MATCH(), not column identifiers.

That won't work.

---

Re your comment:

MATCH(title_translations.title,collection.title) wouldn't work anyway because the columns you list must belong to a single fulltext index, and each index belongs to one table. You can't list columns from different tables. Also you must list all the columns defined, if you defined a multi-column fulltext index.

I assume in your case, you have one fulltext index defined for the single column title in each table.

You will need this:

WHERE MATCH(title_translations.title) AGAINST('string')
   OR MATCH(collection.title) AGAINST('string')

You must do this in two matching terms, so you must repeat the AGAINST in each term.

But I'm not sure if that does what you intend. It isn't clear from your original question.

---

Re your clarification:

If the title exists in title_translations, string need to be matched against title_translations.title and only against it. If the title doesn't exist in title_translations, string need to be matched against collection.title

This is what I come up with:

SELECT x.title FROM 
(
    SELECT IF(t.title IS NOT NULL,
      IF(MATCH(t.title) AGAINST('string') > 0, t.title, NULL),
      IF(MATCH(c.title) AGAINST('string') > 0, c.title, NULL)
    ) AS title
    FROM collection AS c
    LEFT OUTER JOIN title_translations AS t
      ON t.ref_collection = c.id
) AS x
WHERE x.title IS NOT NULL