https://kotlinlang.org logo
y

Yoavya

08/04/2020, 7:31 AM
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
Copy code
count(*) filter ( where columnX in ('a', 'b') ) as custom_count
is there an easy way to achieve this? Thanks 🙂
j

Joel

08/04/2020, 11:34 AM
@Yoavya how would you write this in SQL? If you can show me the example query I can help you translate it for Exposed!
y

Yoavya

08/04/2020, 12:36 PM
Copy code
SELECT COUNT(*) as count, count(*) filter ( where answer.answer in ('yes', 'no') ) as count_having
FROM answer;
this is in Postgres
j

Joel

08/04/2020, 1:28 PM
Ah, I am actually not familiar with that one. Is that a postgres thing?
y

Yoavya

08/04/2020, 1:29 PM
I think so. yes
j

Joel

08/04/2020, 1:29 PM
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
t

tapac

08/04/2020, 10:01 PM
@Yoavya, I'm not sure what it will work, but you could try:
Copy code
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()
}
y

Yoavya

08/05/2020, 5:27 AM
thanks 🙂
j

Joel

08/07/2020, 9:05 PM
This is pretty neat! Wonder if it works for other aggregations...
4 Views