I'm at the stage where I need to write a custom co...

# komapper

[stefano]

I'm at the stage where I need to write a custom column expression to map functions available in ClickHouse to komapper. Specifically, ClickHouse supports functions in the form of, <aggregateFn>If(column, filter) , e.g. sumIf(revenue, period = 'current'). I don't know how to model the filter part in a custom column expression, I'd have structured it as a WhereDeclaration but it doesn't seem to be used as an argument to construct an Operand for the custom column expression. Any ideas how I can do this?

[dave08]

Probably just an enum class Period { current, ... } and use that as a parameter for the custom function?

[dave08]

Same for the operator...

[dave08]

You can always use the WhereDeclaration and find a way to translate it to text and use append(string) to append the string to it... but I wonder if it's worth complicating things since not all of the where features would be needed/available in that context...

[stefano]

not all of the where features would be needed/available in that context...

I'm not so sure about this, I think the implementation of these combinators are quite generic and aimed at avoiding subqueries https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-if

[stefano]

find a way to translate it to text

that's probably where I need help, as a I don't understand how this gets converted to text in the first place in a normal where clause. Any pointer would be appreciated

[stefano]

I guess I'd have to get back the criteria stored in the WhereDeclaration but I don't see how to do that since the support variable is private

[dave08]

and aimed at avoiding subqueries

whereas Where probably includes subqueries... it could be that you're looking for the Criterion sealed interface

[stefano]

yep could be indeed

[dave08]

and BuilderSupport

[dave08]

which seems to actually make the sql from it

[stefano]

so I think I'm getting stuck on this and would need a hint on how to make it work. Let me recap what I need and what I've tried.

I need to write a custom function sumIf that accepts a column expression and a where condition (I verified, ClickHouse can process also subqueries in that condition so it's not just a list of criteria).
The question I cannot currently answer is: how do I get to "render" that where condition in the resulting column expression?

Ideally I'd use BuildSupport but it doesn't seem to be very easy to instantiate inside the custom function (it requires a lot of context I don't have access to inside the function).

I could wrap the where statement into an Operand.Subquery but that also requires at least a select clause to make it an actual subquery.

Another option, which seems more elegant to me, would be to either expose an additional method to SqlBuilderScope, or add another Operand specifically for criteria. But I'm not sure if this breaks the design and the safety of the API.

Not really sure where to go from here, any thoughts?

[Toshihiro Nakamura]

Could you please tell me the full SQL expression that you want to represent in a Komapper query?

[stefano]

this is a simplified version that expresses the need for the particular function. Let's assume there's a table

CREATE TABLE default.orders
(
    `order_id` Int64,
    `client_id` Int64,
    `date` Date,
    `revenue` Float64,
    `costs` Float64,
    `profit` Float64,
    `order_id_external` String
)

I want to run a query like:

select sumIf(revenue, date > 2023-10-01) from orders

the expression in the argument of sumIf is a proper WhereDeclaration in Komapper parlance, i.e. I could also write something like:

select sumIf(revenue, (order >= 100 and order in (select order_id from orders))) from sales_targets_employees;

[Toshihiro Nakamura]

Thank you for sharing your SQL statement with me.
Try to modify SqlBuilderScope and its implementation as follows:

interface SqlBuilderScope {

    val dialect: Dialect

    /**
     * Appends an SQL fragment.
     */
    fun append(text: CharSequence)

    /**
     * Deletes characters from the SQL string.
     *
     * @param length character length
     */
    fun cutBack(length: Int)

    /**
     * Processes an operand.
     */
    fun visit(operand: Operand)
    
    fun where(declaration: WhereDeclaration)
}

internal class SqlBuilderScopeImpl(
    override val dialect: Dialect,
    private val buf: StatementBuffer,
    private val builderSupport: BuilderSupport,
) : SqlBuilderScope {
    override fun append(text: CharSequence) {
        buf.append(text)
    }

    override fun cutBack(length: Int) {
        buf.cutBack(length)
    }

    override fun visit(operand: Operand) {
        builderSupport.visitOperand(operand)
    }

    override fun toString(): String {
        return buf.toString()
    }

    override fun where(declaration: WhereDeclaration) {
        val support = FilterScopeSupport(::WhereScope)
        WhereScope(support).apply(declaration)
        val criteria = support.toList()
        if (criteria.isNotEmpty()) {
            for ((index, criterion) in criteria.withIndex()) {
                builderSupport.visitCriterion(index, criterion)
                buf.append(" and ")
            }
            buf.cutBack(5)
        }
    }
}

[stefano]

can I open a PR if it works?

[stefano]

@Toshihiro Nakamura I think I'll also come around to write a proper dialect for ClickHouse in the next couple of months. Let me know if you're interested in me submitting a PR to add support - from what I'm seeing apart from the two issues we've been discussing everything else works fine

[Toshihiro Nakamura]

can I open a PR if it works?

Of course, you’re welcome to create a PR, but I think it would be better to merge it into the main branch after the Dialect for ClickHouse is completed.

Let me know if you’re interested in me submitting a PR

If you provide a PR with integration tests using Testcontainers, I wouldn’t mind incorporating it. I would be pleased if you could take on the responsibility for its continuous maintenance.

[stefano]

ok I think it's something I can definitely consider. My schedule is a bit tight atm due to small baby at home but I like the komapper project and there's nothing out there supporting ClickHouse officially so I'm happy to make my own contribution

[stefano]

I just need to check how this plays with the customer's schedule, as without support for the above function I'm kinda constrained in my use of komapper. But maybe I can find a workaround for right now to make it work for my specific case, and have it implemented properly in the PR for ClickHouse support