https://kotlinlang.org logo
Join Slack
Powered by
<@U01SP2GJYAU> How could I write these in QueryDsl...
# komapper
d
@Toshihiro Nakamura How could I write these in QueryDsl:
Copy code
select...
                    ((FLOOR(RANDOM() * (100 - 0 + 1)) + 5)::BIGINT) AS random,
                    (OTHER.tags_token::INT[] & ?::INT[]) AS similarity
from ...
I'm really wondering about how to express casting like ::BIGINT and arrays like ::INT[]... also there's a 
?::INT[]
...
t
d
Yeah, but in this case, templates are harder because the query is very dynamic... How do I handle the 
?:INT[]
operator? The 
?
here is a list or collection type...
Is this the way to do it for casting @Toshihiro Nakamura?
Copy code
enum class PostgresType(val typeStr: String) {
    BIGINT("BIGINT"),
    INT_ARRAY("INT[]")
}

private inline fun <reified S : Any, reified D : Any, reified INNER : Any> cast(
    expression: ColumnExpression<S, INNER>,
    destinationType: PostgresType,
): ColumnExpression<D, INNER> {
    val name = "cast"
    val o1 = Operand.Column(expression)
    return UserDefinedExpression(
        typeOf<D>(),
        typeOf<INNER>(),
        { it as D },
        name,
        listOf(o1),
    ) {
        visit(o1)
        append("::${destinationType.name}")
    }
}
Also, 
(FLOOR(RANDOM() * (100 - 0 + 1)) + 5)::BIGINT
seems pretty complex to implement... I really just need to dump it as one of the columns... any way to do that?
t
How do I handle the 
?:INT[]
operator? The 
?
here is a list or collection type...
There are various ways to approach it, but treating it as a string might be a good option.
Copy code
enum class PostgresType(val typeStr: String) {
    BIGINT("BIGINT"),
    INT_ARRAY("INT[]")
}

private fun cast(
    value: String,
    destinationType: PostgresType,
): ColumnExpression<String, String> {
    val name = "cast"
    val o1 = Operand.SimpleArgument(typeOf<String>(), value)
    return columnExpression({it}, name, listOf(o1)) {
        append("(")
        visit(o1)
        append("::${destinationType.typeStr})")
    }
}
the query is very dynamic...
Rather than expressing it in a single query, it might be better to split it into multiple queries.
d
Oh.. to use your function with: 
cast("(FLOOR(RANDOM() * (100 - 0 + 1)) + 5)", <http://PostgresType.INT|PostgresType.INT>_ARRAY)
? But then how would I handle an actual db field or Operator type (
?
) if it would take in a String type?
Rather than expressing it in a single query, it might be better to split it into multiple queries.
There's other cases where I'm using templates heavily, but in this case, I need to add joins and orders on the fly and the dsl makes this much nicer and safer, and I can unit test the result much easier.
Nope I see that your function generates a ?::BIGINT
So the FLOOR... example is a bit harder..
t
You can write as follows:
Copy code
val query = QueryDsl.select(
    floor(
        random() *
                (literal(BigDecimal(100))
                        - literal(BigDecimal(0))
                        + literal(BigDecimal(1)))
                + literal(BigDecimal(5))
    )
)

private fun floor(
    expression: ColumnExpression<BigDecimal, BigDecimal>,
): ColumnExpression<BigDecimal, BigDecimal> {
    val name = "floor"
    val o1 = Operand.Column(expression)
    return columnExpression(expression, name, listOf(o1)) {
        append("floor(")
        visit(o1)
        append(")")
    }
}
Operator type (
?
)
Is 
?
an operator? Isn’t it a bind parameter?
d
Nice 👌🏼! I guess I still have to figure out a bunch about this custom column feature... the docs only give a simple example... but I'm on my way!
t
Indeed, there is room for improvement in the documentation. Pull requests are welcome. https://github.com/komapper/komapper-docs/blob/main/content/en/docs/Reference/Query/QueryDsl/expression.md
d
I really should... if I have a bit of time, maybe I'll try to hack at it a bit.
8 Views
Open in Slack
PreviousNext
Powered by