Kysely•2y ago

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)

   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"
    );

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

11 Replies

Igal•2y ago

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

Daniel CruzOP•2y ago

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

Igal•2y ago

Feel free to ask anything! 🙂

Daniel CruzOP•2y ago

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

Igal•2y ago

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

Daniel CruzOP•2y ago

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

Igal•2y ago

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

Daniel CruzOP•2y ago

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"

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"),

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

Igal•2y ago

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.

Daniel CruzOP•2y ago

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'

 -- ❌ 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"),

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

Igal•2y ago

This is better (type-safety):

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

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

Gaming

Programming

Join DATE_TRUNC