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
Daniel Cruz
Daniel Cruz15mo ago
Yeah that works, thanks a lot! I'll probably ask more questions later in other threads if thats ok on 😅
Igal
Igal15mo ago
Feel free to ask anything! 🙂
Daniel Cruz
Daniel Cruz15mo ago
what's the difference on making the query with and without ref. Seems that the produced output is the same
Igal
Igal15mo ago
Can you be more specific? ref can mean 2 different things
Daniel Cruz
Daniel Cruz15mo 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
Igal15mo ago
that's my bad here, eb.fn treats strings as refs
Daniel Cruz
Daniel Cruz15mo 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
Igal15mo 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 Cruz
Daniel Cruz15mo 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
Igal14mo 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')