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)")
    }
}

[rushmi0]

https://youtrack.jetbrains.com/issue/EXPOSED-477/I-use-Exposed-to-perform-a-full-text-search-in-PostgreSQL

[bog-walk]

For reference, here is a link to docs on string pattern matching, namely use of the standards like, notLike, and regexp functions.

For the more database-specific text search functions, here's a link to another answered YT question, EXPOSED-347.

In the event that viewing YouTrack comments is tedious, please find some example function implementations below (using current version 0.53.0).

---

PostgreSQL example:

To implement @@ (or any operator that returns a boolean), you'll need to implement a custom operator:

class TSMatchOp<T : String?>(
    expr1: Expression<T>,
    expr2: Expression<T>
) : ComparisonOp(expr1, expr2, "@@")

infix fun <T : String?> Expression<T>.tsMatches(other: Expression<T>) = TSMatchOp(this, other)

Then to implement any custom function, the easiest short-term option is to use function():

Books.title.function("to_tsvector")

But a more long-term solution would be to extend CustomFunction (or CustomStringFunction) directly:

class ToTSVector<T : String?>(
    config: Expression<T>?,
    document: Expression<T>
) : CustomFunction<String?>(
    "to_tsvector",
    TextColumnType(),
    *config?.let { arrayOf(config, document) } ?: arrayOf(document)
)

fun <T : String?> Expression<T>.toTSVector(config: Expression<T>? = null) = ToTSVector(config, this)

class ToTSQuery<T : String?>(
    config: Expression<T>?,
    query: Expression<T>
) : CustomFunction<String?>(
    "to_tsquery",
    TextColumnType(),
    *config?.let { arrayOf(config, query) } ?: arrayOf(query)
)

fun <T : String?> toTSQuery(query: Expression<T>, config: Expression<T>? = null) = ToTSQuery(config, query)

// alternatively, to_tsquery could be defined as below if string literals are only ever passed as arguments
// to avoid using stringParam() in every query below
// fun toTSQuery(query: String, config: Expression<String>? = null) = ToTSQuery(config, stringParam(query))

Then these could be used as follows:

Books
    .selectAll()
    .where {
        Books.body.toTSVector() tsMatches toTSQuery(stringParam("friend"))
    }
    .toList()
// SELECT books.title, books.body FROM books WHERE to_tsvector(books.body) @@ to_tsquery('friend')

val config = stringLiteral("english")
Books
    .selectAll()
    .where {
         Books.body.toTSVector(config) tsMatches toTSQuery(stringParam("friend"), config)
    }
    .toList()
// SELECT books.title, books.body FROM books WHERE to_tsvector('english', books.body) @@ to_tsquery('english', 'friend')

val titleOrBody = concat(Books.title, stringLiteral(" "), Books.body)
Books
    .selectAll()
    .where {
        titleOrBody.toTSVector() tsMatches toTSQuery(stringParam("create & table"))
    }
    .toList()
// SELECT books.title, books.body FROM books WHERE to_tsvector(CONCAT(books.title, ' ', books.body)) @@ to_tsquery('create & table')

MySQL example:

enum class FTSearchModifier(val sql: String) {
    NATURAL_LANGUAGE("IN NATURAL LANGUAGE MODE"),
    NATURAL_LANGUAGE_WITH_QUERY("IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION"),
    BOOLEAN("IN BOOLEAN MODE"),
    WITH_QUERY("WITH QUERY EXPANSION")
}

class MatchAgainstOp<T : String?>(
    val query: String,
    vararg val columns: Column<T>,
    val modifier: FTSearchModifier?
) : Op<Boolean>(), ComplexExpression {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            columns.appendTo(prefix = "MATCH(", postfix = ")") { +it }
            +" AGAINST("
            +stringParam(query)
            modifier?.let { +" ${it.sql}" }
            +")"
        }
    }
}

fun <T : String?> matchAgainst(
    query: String,
    vararg columns: Column<T>,
    modifier: FTSearchModifier? = null
) = MatchAgainstOp(query, columns = columns, modifier)

// example usage
Books
    .selectAll()
    .where { matchAgainst("MySQL", Books.title, Books.body, modifier = FTSearchModifier.NATURAL_LANGUAGE) }
    .toList()