Full text search opportunity

[sergeybannikov]

We did not found any functionality for making full text search queries or configurating. Also it would be useful to make simple string queries via dsl objects. Do you have any ideas or workarounds? We use Postgres.

[peterwilli]

I also want to use or find this feature or at least a workaround. I'm using SQLite. Exposed is really nice but it really has it's limitations when it comes to basic features you expect to be there :(

[peterwilli]

@Aileron1410 I know it's almost 2 years ago but I found it. Posting it here as solution for others. You can use % as wildcarts.

var results = MyObject.find { Object.myText like "%stringInText%" }

[mariolourobert]

An another solution seems to work well :

val query = MyTable.select {
    MyTable.myText.regexp(stringParam(stringInText), false)
}

The last param in regexp function is for "caseSensitive"

[Intex32]

That's my code for now. It generates SQL similar to this MATCH (col) AGAINST ('input' IN NATURAL LANGUAGE MODE) Tested with MySQL. Not perfect, but better than using wildcards IMO.

/**
 * full text search for MySQL database
 * requires indexes on [columns]
 */
fun matchFullTextSearch(
    q: String,
    vararg columns: Column<*>,
) = FullTextSearch(q, columns.toList())

class FullTextSearch(
    private val q: String,
    private val columns: List<Column<*>>,
) : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder.run {
        append("MATCH (")
        columns.forEachIndexed { i, col ->
            col.toQueryBuilder(queryBuilder)
            if(columns.lastIndex != i)
                append(",")
        }
        append(")")
        append(" AGAINST (")
        append(stringLiteral(q))
        append(" IN NATURAL LANGUAGE MODE)")
    }
}