Can't join FREETEXTTABLE with typeORM functions

Question

I'm working on a graphQL backend with TypeORM I found FREETEXT, CONTAiNS, FREETEXTTABLE and CONTAINSTABLE options for fulltext searching in my SQL database. As FREETEXTTABLE has a "RANK column, it is more useful and I’m using this option. I added required settings to the database and by below query it’s working correctly when applying the query directly into the database:

SELECT * FROM content
INNER JOIN freetexttable( content , *, 'test text') newtable ON newtable.[KEY] = "id"
ORDER BY newtable.RANK desc 
OFFSET 0 ROWS FETCH NEXT 12 ROWS ONLY

But when I try to use it with TypeORM like below I have some errors.

import { getRepository } from "typeorm";

let repo = getRepository(Content)

return repo.createQueryBuilder("qb")
.innerJoin(FREETEXTTABLE( qb , *, 'light'), newtable,newtable.[KEY] = qb.id)
.skip(0)
.take(12)
.getManyAndCount();

And it returns this error:

"Error: Invalid object name 'FREETEXTTABLE( qb , *, 'light')'."

Could you please let me know if there is a problem with my code or maybe you know a better option for fulltext search in SQL database

Answer 1

I was searching for the same issue. I was unable to find a solution to use FREETEXTTABLE with typeorm, so I had to implement it using raw SQL. using below code

repo.query(" RAW SQL ")