How to write a where clause using a function

[efenderbosch]

I want to add a where clause using a function and I'm not sure how to do it.

Raw SQL:

SELECT *
FROM my_table
where @time < TIMESTAMPADD(MINUTE, duration, start_time)

Is this possible? Can I get a little guidance if so? Thanks.

[efenderbosch]

I got something working, but I'm not sure if this is the right way to do it.

enum class TimestampAddUnit(val mysqlName: String) {
    Microsecond("MICROSECOND"),
    Second("SECOND"),
    Minute("MINUTE"),
    Hour("HOUR"),
    Day("DAY"),
    Week("WEEK"),
    Month("MONTH"),
    Quarter("QUARTER"),
    Year("YEAR")
}

fun timestampAdd(
    durationColumn: Column<NumberOfMinutes>,
    unit: TimestampAddUnit,
    timeColumn: Column<LocalTime>
): FunctionExpression<LocalTime> {
    return FunctionExpression(
        functionName = "TIMESTAMPADD",
        arguments = listOf(
            LiteralSqlExpression(unit.mysqlName),
            durationColumn.asExpression(),
            timeColumn.asExpression()
        ),
        sqlType = LocalTimeSqlType
    )
}

class LiteralSqlExpression(val literal: String): ScalarExpression<String>() {
    override val extraProperties: Map<String, Any>
        get() = emptyMap()
    override val isLeafNode: Boolean
        get() = true
    override val sqlType: SqlType<String>
        get() = VarcharSqlType
}

class CustomSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int) : MySqlFormatter(
    database,
    beautifySql,
    indentSize
) {
    override fun visitUnknown(expr: SqlExpression): SqlExpression {
        return when (expr) {
            is LiteralSqlExpression -> {
                write(expr.literal)
                return expr
            }
            else -> super.visitUnknown(expr)
        }
    }
}

And usage:

    inShiftSchedule.map {
        conditions.add(ShiftScheduleTable.startTime greaterEq it)
        conditions.add(
            it less timestampAdd(
                ShiftScheduleTable.startTime,
                ShiftScheduleTable.duration,
                TimestampAddUnit.Minute
            )
        )
    }

Where inShiftSchedule is an Option (Some/None semantics).

It correctly adds (? < TIMESTAMPADD(MINUTE, shift_schedule.duration, shift_schedule.start_time)) to the where clause.

[vincentlauvlwj]

Awesome, you are right!

[JogoShugh]

Thanks for sharing this. It helped me get somewhere with what I'm trying. But, I am not sure if it will be the best way, and I have another question too.

I'm trying to produce SQL like this for SQL Server:

SELECT C.FIELD_DEFAULT, 0 AS FIELD_REQUIRED, ISNULL(C.FIELD_ALLOW, 0) AS FIELD_ALLOW

I think I got the ISNULL part working, though not very generically, by adapting what I found in this thread like this:

fun isNull(
    column: Column<Boolean>,
    default: Int
): FunctionExpression<Boolean> = FunctionExpression(
        functionName = "ISNULL",
        arguments = listOf(
            column.asExpression(),
            IntSqlExpression(default)
        ),
        sqlType = org.ktorm.schema.BooleanSqlType
    )

class IntSqlExpression(val value: Int): ScalarExpression<Int>() {
    override val extraProperties: Map<String, Any> get() = emptyMap()
    override val isLeafNode: Boolean get() = true
    override val sqlType: SqlType<Int> get() = IntSqlType
}

class CustomSqlFormatter(database: Database, beautifySql: Boolean, indentSize: Int)
    :  org.ktorm.support.sqlserver.SqlServerFormatter(database, beautifySql, indentSize) {

    override fun visitUnknown(expr: SqlExpression): SqlExpression {
        return when (expr) {
            is IntSqlExpression -> {
                write(expr.value.toString())
                return expr
            }
            else -> super.visitUnknown(expr)
        }
    }
}

But, I'm really not sure what to do to be able to produce the seemingly simple 0 AS FIELD_REQUIRED part. Any ideas?
Thanks!