Yoavya

    Yoavya

    2 years ago
    Hi, I want to create a custom count, normally when I want to count a column I just
    columnX.count()
    in the slice part of the query. I want to do something like this
    count(*) filter ( where columnX in ('a', 'b') ) as custom_count
    is there an easy way to achieve this? Thanks 🙂
    Joel

    Joel

    2 years ago
    @Yoavya how would you write this in SQL? If you can show me the example query I can help you translate it for Exposed!
    Yoavya

    Yoavya

    2 years ago
    SELECT COUNT(*) as count, count(*) filter ( where answer.answer in ('yes', 'no') ) as count_having
    FROM answer;
    this is in Postgres
    Joel

    Joel

    2 years ago
    Ah, I am actually not familiar with that one. Is that a postgres thing?
    Yoavya

    Yoavya

    2 years ago
    I think so. yes
    Joel

    Joel

    2 years ago
    If it's unique to postgres then my guess is that it isn't supported by Exposed out of the box, probably need a custom function
    I know you can do a correlated subquery using
    wrapAsExpression
    tapac

    tapac

    2 years ago
    @Yoavya, I'm not sure what it will work, but you could try:
    fun CountWithFilter(condition: SqlExpressionBuilder.() -> Op<Boolean>) = 
        CustomLongFunction("count(*) filter", SqlExpressionBuilder.condition())
    
    fun main() {
        val countFilter = CountWithFilter { AnswerTable.answer inList listOf("yes", "no") }
    AnserTable.slice(countFilter).selectAll()
    }
    Yoavya

    Yoavya

    2 years ago
    thanks 🙂
    Joel

    Joel

    2 years ago
    This is pretty neat! Wonder if it works for other aggregations...