How do we compose queries in Kotlin Exposed?

Question

I would like to create something similar to Ruby's ActiveRecord Scopes using Kotlin Exposed.

For example I would like to break the following query up so that the first part acts like a scope.

This query returns what I want.

val m1 = Measurement.wrapRows(Measurements.innerJoin(Runs).select {
        ((exists(Tags.select { Tags.run.eq(Runs.sequelId) and Tags.name.eq("region") and Tags.value.eq("default") })) or notExists(Tags.select {
            Tags.run.eq(Runs.sequelId) and Tags.name.eq("region")
        })) and Measurements.name.eq("someName")

I would like to use this part as a scope:

val q1 = Measurements.innerJoin(Runs).select {
    ((exists(Tags.select { Tags.run.eq(Runs.sequelId) and Tags.name.eq("region") and Tags.value.eq("default") })) or notExists(Tags.select {
        Tags.run.eq(Runs.sequelId) and Tags.name.eq("region")
    }))
}

and then be able to refine the query using the q1 "scope"

so something like this:

val q2 = q1.having { Measurements.name.eq("someName")  } // which does not work

Ultimately I would like to push this down into either the Measurements object or the Measurement class so I can do something like this

Measurement.withDefaultRegion.where( Measurements.name.eq("someName")

nPn · Accepted Answer · 2018-08-10T13:56:37.177

I was able to get what I wanted by adding a couple of functions to the model's companion object.

The first one provides the "scope"

fun defaultRegion() :Op<Boolean> {
    return Op.build {(exists(Tags.select { Tags.run.eq(Runs.sequelId) and Tags.name.eq("region") and Tags.value.eq("default") })) or notExists(Tags.select {
        Tags.run.eq(Runs.sequelId) and Tags.name.eq("region")
    })}
}

The second function does the query using the scope and any refinements passed in and returns a "collection" of objects.

fun withDefaultRegionAnd( refinedBy: (SqlExpressionBuilder.()->Op<Boolean>)) : SizedIterable<Measurement> {
    return Measurement.wrapRows(Measurements.innerJoin(Runs).select(Measurement.defaultRegion() and SqlExpressionBuilder.refinedBy() ))
}

At the client level I can simply do this:

val measurements = Measurement.withDefaultRegionAnd { Measurements.name.eq("someName") }

Here are the nearly table object and entity classes:

object Measurements : IntIdTable("measurements") {

    val sequelId = integer("id").primaryKey()
    val run = reference("run_id", Runs)
    // more properties
}

class Measurement(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Measurement>(Measurements) {
        fun defaultRegion() :Op<Boolean> {
            return Op.build {(exists(Tags.select { Tags.run.eq(Runs.sequelId) and Tags.name.eq("region") and Tags.value.eq("default") })) or notExists(Tags.select {
                Tags.run.eq(Runs.sequelId) and Tags.name.eq("region")
            })}
        }
        fun withDefaultRegionAnd( refinedBy: (SqlExpressionBuilder.()->Op<Boolean>)) : SizedIterable<Measurement> {
            return Measurement.wrapRows(Measurements.innerJoin(Runs).select(Measurement.defaultRegion() and SqlExpressionBuilder.refinedBy() ))
        }
    }

    var run by Run referencedOn Measurements.run
    var name by Measurements.name
    // more properties
}

How do we compose queries in Kotlin Exposed?

1 Answers1