Join DATE_TRUNC

Hey, sorry if this is already answered somewhere. I couldn't find anything 😦

I want to translate this part of a SQL query into Kysely

   LEFT JOIN currency cu ON DATE_TRUNC('day', s.timestamp) 

I tried this, but doesn't seem to be working

 .leftJoin(
      "currency as cu",
      sql`DATE_TRUNC('day', s.timestamp)`.as("s.timestamp"),
      "cu.timestamp"
    );

Hey 👋

Try this: https://kyse.link/?p=s&i=I2oKCdnuMtLMgnTfFE44

Yeah that works, thanks a lot! I'll probably ask more questions later in other threads if thats ok on 😅

Feel free to ask anything! 🙂

what's the difference on making the query with and without ref. Seems that the produced output is the same

Can you be more specific? ref can mean 2 different things

Sure, sorry about the ambiguity. In the link you shared the eb ref function is used.

However if I remove it, I get the same query

that's my bad here, eb.fn treats strings as refs

Ok, so as long as I'm inside of eb.fn I can use plain strings? And another question,

What's the best way to translate this?

SUM(s.price * cu.daily_price) AS "Volume USD"

I'm doing this, but obviously TS is complaining

({ fn }) => fn("sum", ["s.price * cu.daily_price"]).as("volume_usd"),

yes, when referring to columns in query context.. otherwise you need to escape it (e.g. sql.lit('some_string') or eb.val('some_string'))

.lit opens you up for sql injection, so make sure you use it with care.

Ok, thanks so much again!

sql.lit doesn't work because I get the query with quotes

 -- ❌ not valid
 sum('s.price * cu.daily_price') as 'volume_usd'

I used sql instead, unless there's a better way.

({ fn }) => fn("sum", [sql`s.price * cu.daily_price`]).as("volume_usd"),

This is better (type-safety):

({ bxp, fn, ref }) => fn<number>('sum', [bxp('s.price', '*', ref('cu.daily_price'))]).as('volume_usd')