How to add multiple or filter conditions based on incoming parameters using exposed?

Question

I have to add or conditions using the parameter values to the query.

Example: select * from users where email = "abc@xyz.com" or phone="1234123412";

The user might send both fields or only one. I want to do this in a loop for each field and add each one of them in or where condition.

val query = Users.selectAll()
**var predicates = Op.build { Users.id inList listOf<Int>()}**
for((k, v) in params) {
    val value = URLDecoder.decode(v.first(), "UTF-8")
    predicates = when(k) {
        "email" -> predicates.or(Users.email eq value)
        "phone" -> predicates.or(Users.phone eq value)
    }
}
query.andWhere { predicates }

The above DSL produces the below SQL.

SELECT * from users where (((false = true) OR (users.email = "abc@xyz.com")) OR (users.phone = "1234567890"))

See that false = true? That is because, to use .or method, I have to initialize with a condition. The below given snippet is an unncessary line of code added to initialize the predicate.

var predicates = Op.build { Users.id inList listOf<Int>()}

What is the right way to initialize it so that I can seamlessly add multiple or and and predicates to the query?

AFAIK virtually all frameworks/libraries that end up creating SQL use that kind of clauses. They are simply better from a DSL point of view. They remove special handling of `or` and `and` if there is 0 or 1 conditions. The SQL engine will simply ignore them so there is no real downside there. Just look at the queries generated by, say, hibernate. They are filled with `1=0` and `1=1` and stuff like that. So chances are you cannot remove them easily. — Giacomo Alzetta, Jan 25 '19 at 08:32

Tapac · Accepted Answer · 2019-11-02T14:44:15.723

First of all, I would advice to optimize your code like:

    val params = mapOf<String, List<String>>()
    val emails = params.filterKeys { it == "email" }.map { URLDecoder.decode(it.value.first(), "UTF-8") }
    val phones = params.filterKeys { it == "phone" }.map { URLDecoder.decode(it.value.first(), "UTF-8") }

    if (emails.isEmpty() && phones.isEmpty()) {
        error("No suitable params were provided")
    } else {
        Users.select {
            Users.email inList emails or (Users.phone inList phones)
        }
    }

UPD: orWhere function is available in Exposed since 0.16.1 version.

If you want to use pattern-matching (when) please define your local orWhere function the same way andWhere works:

fun Query.orWhere(andPart: SqlExpressionBuilder.() -> Op<Boolean>) = adjustWhere {
    val expr = Op.build { andPart() }
    if(this == null) expr
    else this or expr
}

And use it like:

val query = Users.selectAll()
for((k, v) in params) {
    val value = URLDecoder.decode(v.first(), "UTF-8")
    predicates = when(k) {
        "email" -> query.orWhere { Users.email eq value }
        "phone" -> query.orWhere{ Users.phone eq value }
    }
}

score 2 · Answer 2 · answered Jan 25 '19 at 10:57

A trick would be to initialize predicates to null and define your own or and and on Op<Boolean>?:

// not sure x's type is correct here, the wiki doesn't give exact signatures
// may also need @JvmName to allow overloading
fun Op<Boolean>?.or(x: Op<Boolean>) = if (this != null) this.or(x) else x

...
var predicates: Op<Boolean>? = null
...
predicates = when(k) {
    "email" -> predicates.or(Users.email eq value)
    "phone" -> predicates.or(Users.phone eq value)
}

Whether it's worth the complication, I doubt: as Giacomo's comment mentions, the database will certainly optimize your original false = true out.

How to add multiple or filter conditions based on incoming parameters using exposed?

2 Answers2

Linked