Generating Raw Date Conditions

[rcronin]

Hello,

I'm using a product called Metabase and working to get a custom driver working. There's a method I'm overriding that constructs logic getting last X weeks. Below is an example of what I'm trying to accomplish

(defmethod sql.qp/date [:db2 :week] [_ _ expr] (hx/- expr (hsql/raw (hsql/format "days" (hsql/call :dayofweek expr)))))

expr here equates to this: (CAST(current timestamp AS timestamp) + -14 days)

When my code above runs, I get this output:

((CAST(current timestamp AS timestamp) + -14 days) - ? days)

My desired output here is: ((CAST(current timestamp AS timestamp) + -14 days) - dayofweek((CAST(current timestamp AS timestamp) + -14 days)) days)

I'm extremely new to clojure and fumbling my way through this. Any advice or help would be greatly appreciated!

Thanks!

[seancorfield]

@rcronin First off, what version of HoneySQL are you using? Also, what is hx/-?

format only works with a full DSL structure -- "days" is not a valid DSL structure.

[rcronin]

@seancorfield Using version 1.0.461

hx/- is an extension written by Metabase. Here's the source:

(def ^{:arglists '([& exprs])} - "Math operator. Interpose -betweenexprs and wrap in parentheses." (partial hsql/call :-))

Makes sense how format works. Do you have any guidance with how I could accomplish the desired SQL outcome?

I've tried this too: (defmethod sql.qp/date [:db2 :week] [_ _ expr] (hx/- expr (hsql/raw (format "%d days" (hsql/call :dayofweek expr)))))

Which results in this error: honeysql.types.SqlRaw cannot be cast to clojure.lang.IFn

[seancorfield]

Ah, that's a very old version of HoneySQL. A lot of it was undocumented so it's really hard for me to provide much guidance.

I'll try to find some time in my next OSS window to look at the 1.x raw behavior and see if I can figure out what's going on here -- but that will likely be a couple of weeks right now.

[rcronin]

@seancorfield I was able to get this working with honeysql.format/to-sql

(defmethod sql.qp/date [:db2 :week] [_ _ expr] (hx/- expr (hsql/raw (format "%s days" (hformat/to-sql (hsql/call :dayofweek expr))))))

[seancorfield]

Glad you got it working! I'm sure it would be a lot easier in HoneySQL 2.0 since you no longer need hsql/call and there's a built-in :cast operator now as well:

dev=> (sql/format-expr [:cast [:raw "current timestamp"] :timestamp])
["CAST(current timestamp AS timestamp)"]
dev=> (sql/format-expr [:raw [[:dayofweek :expr] " days"]])
["DAYOFWEEK(expr) days"]

[seancorfield]

The whole expression, in HoneySQL 2.0, would be:

dev=> (let [expr [:+ [:cast [:raw "current timestamp"] :timestamp] [:raw [-14 " days"]]]]
 #_=>   (sql/format-expr [:- expr [:raw [[:dayofweek expr] " days"]]]))
["(CAST(current timestamp AS timestamp) + -14 days) - DAYOFWEEK(CAST(current timestamp AS timestamp) + -14 days) days"]