Kyselydecho

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

thelinuxlich•321d ago

not annoying at all, thanks for the addition!

decho•321d ago

cheers!

Igal•321d ago

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

decho•321d 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•320d ago

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

decho•319d 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•319d 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')
)

decho•319d 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

decho•319d ago

Igal•319d ago

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

decho•319d 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•316d ago

basically anything with a .toOperationNode() method.

decho•316d ago

lesson learned (and hopefully remembered) 😄

Kysely Join

1.4KMembers

View on Discord

Want results from more Discord servers?

Add your server

Can't get filterWhere to work with joins from another table.Hey everyone. I am trying to recreate the following SQL (simplified demo): ```sql SELECT "users".is this uuid() correct in mysql?export async function up(db: Kysely<any>): Promise<void> { await db.schema .createTable("Payme aliases vs strict mode in tsconfigI have a problem with tsconfig. When I have strict mode kysely does not find aliases. What is the "m CTE with exists() and case()Hey guys, just wrote this one and I thought I'd share. ```sql WITH "ongoing" AS ( SELECT Kysely originI have to ask. Where does the “kysely” name come from…?🤷‍♂️ It means “sour” in my mother tongue �coalesce in where statementI've found examples and documentation for coalesce in select however I still struggle how to use it Which approach is better for coalesce and similar functions?The fn and raw looks to me familiar as in sequelize there is fn and literal. What is the right appro Single source of truth (defaultAlias-schema-tableName)Hi, is there any way how to set default alias to schema.table mapping to have single source of truth What's the pattern for writing migrations exactly?I'm not familiar with SQL migrations, so my question is how do we create the migration files, what r writing a transaction that calls functionsHey everyone, I like what I've got going here but before I did this for my whole code base I just wa how to search inside a json type column, I'm using postgresqlI looked in the documentation, and it talks about using the jsonObjectFrom function that is in the k How to inject parentheses in the generated arithmetic expression?https://kyse.link/?p=s&i=SD8v0Fo6NqqCrPEXMbx1 That's obviously not what I meant .withSchema() and raw SQLwhen doing `sql <ReturnType> SELECT some_function(param);.execute(db)` the function is not found be JSON in columns (postgresql)Hey <a:YA_Wave:743393941369651201> I am completely new to using SQL databases and have a (hopefull Escaped paramaters in raw sqlFor a few queries I made postgreSQL functions, this is the current way of how I call this function: Should database tables mimic form fields?Hey everyone, this question is not Kysely specific just wanna point that out don't know where to ask Best practice around building query functionsHey, I've looked around the docs somewhat but still not confident if I should do this: ```ts export Declaring types under 'kysely-codegen' moduleHey everyone, because I'm using Prisma to push schemas to my database (it's because my Zod Schemas) From Prisma Model to Kysely SchemaHey everyone, I'm using this package https://github.com/valtyr/prisma-kysely, the output of this Pri [ERR_UNKNOWN_FILE_EXTENSION] when running migrationsI have a project with Sveltekit, TypeScript, and Kysely with SQLite. When I run my migrations, using

Gaming

Programming

Prediction league leaderboard