I've been looking for a way to create View in Expo...

# exposed

[Fedor Erofeev]

I've been looking for a way to create View in Exposed, however not able to find anything in documentation. Is this possible?

[Chantal Loncle]

Hi @Fedor Erofeev Views do not currently have out-of-the-box support, but this is a feature request that is very much on our radar and it will eventually make it into the plans once our current major feature tasks are completed.

It is possible to customize this functionality yourself in one of 2 ways:
1. From the bottom up, you could create a custom class that implements both ColumnSet() and DDLAware and provide as much or as little functionality as your use case requires.
2. For a quicker set-up, you could extend Table, especially if you wanted to use the view with existing CRUD extension functions.
The example below is a basic starter for option #2:

abstract class View(
    name: String = "",
    val query: AbstractQuery<*>
) : Table(name) {
    // define optional View properties to use in DDL string building

    override fun createStatement(): List<String> {
        val tx = TransactionManager.current()
        val ddl = buildString {
            append("CREATE VIEW ")
            append(tx.identity(this@View))
            append(" AS ")
            append(query.prepareSQL(tx, false))
        }
        return listOf(ddl)
    }

    override fun dropStatement(): List<String> {
        val tx = TransactionManager.current()
        val ddl = buildString {
            append("DROP VIEW ")
            if (currentDialect.supportsIfNotExists) {
                append("IF EXISTS ")
            }
            append(tx.identity(this@View))
        }
        return listOf(ddl)
    }

    // this is just 1 way to map fields in query to accessors in view object
    // so view fields can be used to set values and retrieve ResultRow values
    operator fun <T> Column<T>.getValue(o: View, desc: KProperty<*>): Column<T> {
        return queryFields.firstOrNull { it is Column<*> && it.name == this.name } as? Column<T>
            ?: error("Column ${this.name} not found in query set")
    }

    operator fun <T> IExpressionAlias<T>.getValue(o: View, desc: KProperty<*>): Expression<T> {
        return queryFields.firstOrNull { it is Expression<*> && it.toString() == this.alias } as? Expression<T>
            ?: error("Expression not found in query set")
    }

    private val queryFields = query.set.fields.map { field ->
        when (field) {
            is Column<*> -> Column(this, field.name, field.columnType)
            is IExpressionAlias<*> -> field.aliasOnlyExpression()
            else -> field
        }
    }
}

The above could then be used to create a new View object:

// assuming this table exists:
object Cities : Table("cities") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 64)
    val population = long("population")
    override val primaryKey: PrimaryKey = PrimaryKey(id)
}

// this view can then be created:
object SmallCities : View(
    name = "small_cities",
    query = Cities
        .selectAll()
        .where { Cities.population less 10_000L }
) {
    val id by Cities.id
    val name by Cities.name
    val population by Cities.population

    // register other optional view parameters
}

And the View object could be used like a regular Table if the database allows it:

SmallCities.insert {
    it[name] = "..."
    it[population] = ...
}

SmallCities.update {
    it[name] = name.upperCase()
}

SmallCities
    .select(SmallCities.id, SmallCities.name)
    .where { SmallCities.population greater 8000L }
    .map { it[SmallCities.id] to it[SmallCities.name] }

Btw, the extra getValue() operator is for the possibility that the view query selects more than just columns. Meaning something like this:

val maxCityPop = Cities.population.max().alias("max_pop")

object SmallCities : View(
    // ...,
    query = Cities
        .select(Cities.columns + maxCityPop)
        .where { Cities.population less 10_000L }
        .groupBy(Cities.id)
) {
    // ... other fields
    val maxPop by maxCityPop
}

// so the expression could at least be queried from the view
// and the correct identifier would be used in generated SQL
SmallCities
    .select(SmallCities.id, SmallCities.maxPop)
    // ...

This won't cover all cases as-is, but hopefully it proves to be a good starting point for you if you need to work with views before they are officially supported by Exposed.

[Fedor Erofeev]

@Chantal Loncle This is great! Thank you very much, was struggling with this part - will test this out!