Kysely•2y ago

Prediction league leaderboard

Hey, hope I'm not becoming annoying, but I thought I'd share another query I made, perhaps someone can find it useful. This is a leaderboard for a prediction league. It's using 2 CTEs. In the first one we count occurences where the user predicted the correct result, outcome or first scorer of the match. In the second one we sum and multiply these occurrences by score factor, to distribute points with the following score system: - 1 point for guessing the correct outcome - 3 points for guessing the correct on first scorer - 5 points for guessing the exact result Biggest pain point was figuring out how to have ORs with filterWhereRef. Apparently you can't have WhereExpressionFactory as a param, so I used filterWhere instead. But other than that, pretty happy with the result. Kysely is some next level typescript wizardry ❤️ https://kyse.link/?p=s&i=uIQBw3Ttbo38hGA5NQEh Here is what the query outputs: https://cdn.discordapp.com/attachments/1125059913551458406/1125059914277064734/image.png

12 Replies

thelinuxlich•2y ago

not annoying at all, thanks for the addition!

dechoOP•2y ago

cheers!

Igal•2y ago

Hey 👋 Thanks for sharing! btw, rank is unknown since you missed providing a type for .agg

dechoOP•2y ago

thank you for pointing that out. i did notice this I was getting unknowns and unions in my codebase for the return type, so i managed to fix it pretty easy 🙂 just didn't update the example updated it now

Igal•2y ago

I'm wondering if we should force providing a type for .agg and .fn at least in select clause

dechoOP•2y ago

Interesting question, but I am afraid I don't have a strong opinion about this. For me the unknown is fine, but if kysely forced me to use type casting for .agg or .fn i personally wouldn't mind that either, enforcing strictness is not bad in my opinion but others might disagree. Perhaps this can be configurable when you instantiate new Kysely(...) if you guys ever plan to implement such thing in future releases. on an unrelated note, does anyone have a tip how to cast the following expression to ::decimal?

.select(({ fn }) =>
    fn.agg<number>('rank').over(ob => ob.orderBy('totals.points', 'desc')).as('rank')
)

.select(({ fn }) =>
    fn.agg<number>('rank').over(ob => ob.orderBy('totals.points', 'desc')).as('rank')
)

my full example is here at the very bottom: https://kyse.link/?p=s&i=TxUyLLRdi5d3TDX2lsYo

Igal•2y ago

Have you tried wrapping it in a sql template tag?

.select(({ fn }) =>
    sql<number>`${fn.agg('rank').over(ob => ob.orderBy('totals.points', 'desc'))}::decimal`.as('rank')
)

// or

.select(({ fn }) =>
    sql<number>`(${fn.agg('rank').over(ob => ob.orderBy('totals.points', 'desc'))})::decimal`.as('rank')
)

.select(({ fn }) =>
    sql<number>`${fn.agg('rank').over(ob => ob.orderBy('totals.points', 'desc'))}::decimal`.as('rank')
)

// or

.select(({ fn }) =>
    sql<number>`(${fn.agg('rank').over(ob => ob.orderBy('totals.points', 'desc'))})::decimal`.as('rank')
)

dechoOP•2y ago

nope, it didn't occur to me that i can just wrap all of the chained functions inside sql. thanks a lot, i will give it a try it works, awesome thank you again

dechoOP•2y ago

Igal•2y ago

iirc, you can basically wrap any OperationNodeSource thing in sql template tags

dechoOP•2y ago

hmm, im not familiar with that internal type, but i see what you mean. it just didn't occur to me that chaining methods like that would work inside sql, especially not the over(...) method but good to know now you can do this.

Igal•2y ago

basically anything with a .toOperationNode() method.

Gaming

Programming

Prediction league leaderboard