SQL Server FREETEXTTABLE query to MySQL

Question

I got a Django project that was previously using a SQL Server database. I need to adapt it now for MySQL.

I struggle to implement and find the equivalent for that raw SQL using FREETEXTTABLE.

If anyone could point me in the right direction on any documentation that would be greatly appreciated.

Original SQL Server raw query

sql = "select c.myKey, b.[KEY], b.RANK, a.article_appendix, c.article_appendix as article, c.title_en as t1_en, c.title_fr as t1_fr, a.title_en as t2_en, a.title_fr  as t2_fr from [myapp_collectiveagreementsubarticle] a inner join (SELECT * FROM FREETEXTTABLE ([myapp_collectiveagreementsubarticle], ([content_en]), " + search2 + ")) b on a.myKey = b.[KEY]  inner join [myapp_collectiveagreement] c on a.articleKey_id = c.myKey order by b.RANK desc"
            
searchResult = Collectiveagreement.objects.raw(sql)

Answer 1

Try

from django.db import connection

search_term = '+your_search_term'
sql = """
SELECT 
    c.myKey, 
    b.myKey as KEY, 
    b.score, 
    a.article_appendix, 
    c.article_appendix as article, 
    c.title_en as t1_en, 
    c.title_fr as t1_fr, 
    a.title_en as t2_en, 
    a.title_fr as t2_fr 
FROM 
    myapp_collectiveagreementsubarticle a 
INNER JOIN (
    SELECT 
        myKey, 
        MATCH(content_en) AGAINST(%s IN BOOLEAN MODE) as score 
    FROM 
        myapp_collectiveagreementsubarticle 
    WHERE 
        MATCH(content_en) AGAINST(%s IN BOOLEAN MODE)
) b ON a.myKey = b.myKey  
INNER JOIN 
    myapp_collectiveagreement c ON a.articleKey_id = c.myKey 
ORDER BY 
    b.score DESC;
"""

with connection.cursor() as cursor:
    cursor.execute(sql, [search_term, search_term])
    searchResult = cursor.fetchall()