For some reason {:nest } is bringing empty subquery.
execute query: SELECT `a`.`some_value`, `b`.`some_id` FROM `table_a` `a` WHERE (`a`.`type` = ? AND `a`.`user_id` = ())
18:59:49.231 [main] WARN org.mariadb.jdbc.message.server.ErrorPacket -- Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
See an example I'm trying to write with HoneySQL:
(hsql/format
{:select [:table_a.some_value :table_b.some_id]
:from [[:table_a :a]]
:where [:and
[:= :a.type "SOME_TYPE"]
[:= :a.user_id
{:nest
{:select [[{:case
[[:when [:in :some_status ["STATUS_A" "STATUS_B"]]
{:nest
{:select [:b.user_id]
:from [[:table_b :b]
[:join [:table_c :c] [:= :b.c_id :c.id]]]
:where [:= :b.id :some_id]}}]
[:when [:in :some_status ["STATUS_C" "STATUS_D" "STATUS_E"]]
{:nest
{:select [:b.requester_id]
:from [[:table_b :b]]
:where [:= :b.id :some_id]}}]
[:else nil]]}
:user_id]]}}]]}
{:quoting :mysql})
Am I missing some syntax? I used https://github.com/seancorfield/honeysql/blob/develop/doc/special-syntax.md#nest as reference.
Thank you.Which version of HoneySQL? With your code, I get
Execution error (ExceptionInfo) at honey.sql/format-dsl (sql.cljc:1607).
These SQL clauses are unknown or have nil values: :case(perhaps you need [:lift {:case ...}] here?)[honeysql "1.0.461"]
That's v1, support for :nest has appeared only in v2.
Oooh! Hm... I failed to trust on lein ancient upgrade , sigh.
V1 and v2 have different group and artifact ID.
Ah thanks.
Giving a try here
Note that while the DSL is mostly the same between v1 and v2, there are differences, so it is not entirely a drop-in replacement. Which is why v1 and v2 use both different group/artifact and different nses -- so you can use both side-by-side as you migrate your code one query at a time.
You'll want to read https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1161/doc/differences-from-1-x very carefully.
And, in particular for your original example, case is special syntax in v2 (not a clause, like you're trying to use): https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1161/doc/getting-started/sql-special-syntax-#case
(`case` is called out in the differences page, BTW)