Clojurians Log v2

I am attempting to migrate to honeysql 2 from 1. Many things are working, but I'm having trouble with one very complex CTE: The docs for with state:

The argument to :with (or :with-recursive) is a sequences of pairs, each of a result set name (or description) and a basic SQL statement

(with [:A "some sql thing"]
      [:B "another sql thing"]
      ...)

I don’t think that, as written, has ever been valid with strings.

(with [:A ["some sql thing" its params]] [:B ["another sql thing" more params]]) is more like it.

Can you share exactly what you had with v1 that worked (that no longer works the same in v2)?

The syntax should not have changed between v1 and v2 as far as I’m aware @dcj

There are two CTEs in the V1 tests: https://github.com/seancorfield/honeysql/blob/develop/test/honeysql/core_test.cljc#L16-L62 and here’s the same tests for v2 https://github.com/seancorfield/honeysql/blob/v2/test/honey/sql/helpers_test.cljc#L22-L62

The difference there is that modifiers no longer exists in v2 (`:select-distinct` replaces :select and :modifiers as separate things).

Sorry, I over simplified my example with and confused things. I don't use strings, your re-write is basically indeed what I did both before and now

Sounds like you’ll need to share more of what code you actually have then and compare the V1 and V2 data structures produced and what the formatter does with each. That’s why V2 has different namespaces so you can try them side-by-side.

hi!! is it possible to perform select from values in honeysql? => SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter); ? I am still getting the hang of it.

In V2:

user=> (-> (select :*) (from [(values [[1, "one"] [2, "two"] [3, "three"]]) :t]) (sql/format))
["SELECT * FROM (VALUES (?, ?), (?, ?), (?, ?)) AS t" 1 "one" 2 "two" 3 "three"]

Not sure about the (num,letter) piece at the end… I don’t even understand that SQL syntax…

Here is the raw SQL string I am attempting to model in honey2:

(SELECT fid, pca_raw, dca, X.path[1] AS id, X.geom AS g, ST_M(geom) AS m FROM flts_close CROSS JOIN ST_DumpPoints(t_trajectory) AS X)

(println (sql/format (-> (select :fid :pca_raw :dca [[:raw "X.path[1]"] :id] [:X.geom :g] [[:ST_M :geom] :m])
                                (from :flts_close)
                                (sql.helpers/cross-join [[:ST_DumpPoints :t_trajectory] :X]))))

[SELECT fid, pca_raw, dca, X.path[1] AS id, X.geom AS g, ST_M(geom) AS m FROM flts_close CROSS JOIN ST_DUMPPOINTS(t_trajectory) X]

Apparently yes!

In cross join x as y, the x as y part is just an aliased pair (like other joins). I’ll have a think about that array indexing syntax but I don’t realistically think there’s anything I can do that would make it easier/neater in HoneySQL than wrapping [:raw ..] around the expressions.