How can I dynamically set a WHERE clause based on queryParameters in Exposed?

Viewed 14

I am using Ktor to set up a small API. I am also using Exposed for database operations.

I want to select from the database using inList but only for columns that show up in the queryParameters. Here's how things look so far:

Routes.kt

fun Route.purchaseRouting() {
    route("/purchases") {
        get {
            val params = call.request.queryParameters.toMap()
            val purchases = purchasesDAO.allPurchases(params)
            call.respond(purchases)
        }
    }
}

PurchasesDAO.kt:

override suspend fun allPurchases(params: Map<String, List<String>>): List<Purchase> = DatabaseFactory.dbQuery {
    val ids = params["id"].map { it.toInt() }
    val amounts = params["location"]
    // There are a couple more params but just including these for the concept.

    // Select the correct correct rows from the DB, convert them to purchase objects, and return.
    Purchases.select {
        Purchases.location inList locations!! and Purchases.dates inList dates!!
    }.map(::resultRowToPurchase)
}

This will only work if both location and date are in the queryParameters. But how do I go about only filtering with them only if they exist? For just two parameters it is easy, but I have more columns that I didn't include here. I wish to do this dynamically, if possible.

0 Answers
Related